<?php

/*------------------------------------------------------------------------
# com_invoices - Invoice Manager for Joomla
# ------------------------------------------------------------------------
# author				Germinal Camps
# copyright 			Copyright (C) 2012 - 2016 JoomlaThat.com. All Rights Reserved.
# @license				http://www.gnu.org/licenses/gpl-2.0.html GNU/GPL
# Websites: 			http://www.joomlathat.com
# Technical Support:	Forum - http://www.joomlathat.com/support
-------------------------------------------------------------------------*/

//no direct access
defined('_JEXEC') or die('Restricted access.');

jimport( 'joomla.application.component.model' );

/**
 * The model for the invoices list view
 */
class InvoicesModelInvoices extends JModelLegacy
{

	var $_data;
	var $_total = null;
	var $_pagination = null;
	var $_keywords = null;
	var $_external_type;
	var $_external_id;
	var $_external_ref;

	var $query  = null;
	var $query2 ;
	var $query3 ;
	var $query4 ;
	var $query5 ;
	var $query6 ;

	var $input;

	/**
	* Constructor.
	*
	* Sets user states and such.
	*/
	function __construct(){
		parent::__construct();

		$mainframe = JFactory::getApplication();
		$this->input = $mainframe->input;

		switch($this->input->getInt('type', 1)){
			case 2:
			$scope = "quotes";
			break;
			default:
			$scope = "invoices";
			break;
		}

		// Get pagination request variables
		if($this->input->get('task') != 'save'){
			$this->external_type = $this->input->get('external_type', '', 'string');
			$this->external_id = $this->input->get('external_id', 0, 'int');
			$this->external_ref = $this->input->get('external_ref', '', 'string');
		}
		$limit 				= $mainframe->getUserStateFromRequest('invoices.'.$scope.'.limit', 'limit', 20, 'int');
		$limitstart 		= $mainframe->getUserStateFromRequest('invoices.'.$scope.'.limitstart', 'limitstart', 0, 'int');
		$keywords 			= trim($mainframe->getUserStateFromRequest('invoices.'.$scope.'.keywords','keywords','','string'));
		$filter_order     	= $mainframe->getUserStateFromRequest('invoices.'.$scope.'.filter_order', 'filter_order', 'i.id', 'cmd' );
		$filter_order_Dir 	= $mainframe->getUserStateFromRequest('invoices.'.$scope.'.filter_order_Dir', 'filter_order_Dir', 'DESC', 'word' );
		$cal_start 			= $mainframe->getUserStateFromRequest('invoices.'.$scope.'.cal_start','cal_start','','cmd');
		$cal_end 			= $mainframe->getUserStateFromRequest('invoices.'.$scope.'.cal_end','cal_end','','cmd');
		$status_id 			= $mainframe->getUserStateFromRequest('invoices.'.$scope.'.status_id','status_id','','cmd');
		$currency_id 			= $mainframe->getUserStateFromRequest('invoices.'.$scope.'.filter_currency_id','filter_currency_id',0,'int');

		$this->contact_id 			= $this->input->getInt('contact_id');

		$this->setState('filter_order', $filter_order);
		$this->setState('filter_order_Dir', $filter_order_Dir);
		$this->setState('limit', $limit);
		$this->setState('limitstart', $limitstart);
		$this->setState('status_id', $status_id);
		$this->setState('currency_id', $currency_id);
		$this->setState('keywords', $keywords);
		$this->setState('cal_start', $cal_start);
		$this->setState('cal_end', $cal_end);

	}

	/**
	* Get the total number of rows that match the current query
	* @return integer The total number of rows
	*/
	function getTotal()
	{
		// Load the content if it doesn't already exist
		if (empty($this->_total)) {
			$query = $this->_buildQuery();
			$this->_total = $this->_getListCount($query);
		}
		return $this->_total;
	}

	/**
	* Get the Joomla pagination object for this view
	* @return JPagination The pagination object
	*/
	function getPagination()
	{
		// Load the content if it doesn't already exist
		if (empty($this->_pagination)) {
			jimport('joomla.html.pagination');
			$this->_pagination = new JPagination($this->getTotal(), $this->getState('limitstart'), $this->getState('limit') );
		}
		return $this->_pagination;
	}

	/**
	* Get the current filter for Start date
	* @return string The start date, formatted YYYY-MM-DD
	*/
	function getCalstart(){
		if (empty($this->cal_start)) {
			$this->cal_start = $this->getState('cal_start')	;
		}
		return $this->cal_start;
	}

	/**
	* Get the current filter for End date
	* @return string The end date, formatted YYYY-MM-DD
	*/
	function getCalend(){
		if (empty($this->cal_end)) {
			$this->cal_end = $this->getState('cal_end')	;
		}
		return $this->cal_end;
	}

	/**
	* Get the current filter for status
	* @return string The status filter
	*/
	function getStatusId(){
		if (empty($this->status_id)) {
			$this->status_id = $this->getState('status_id')	;
		}
		return $this->status_id;
	}

	/**
	* Get the current filter for Keywords
	* @return string The keywords
	*/
	function getKeywords(){
		if (empty($this->_keywords)) {
			$this->_keywords = $this->getState('keywords')	;
		}
		return $this->_keywords;
	}

	/**
	* Get the current filter for order
	* @return string The order filter
	*/
	function getFilterOrder(){
		return  $this->getState('filter_order') ;
	}

	/**
	* Get the current filter for order direction
	* @return string The order direction
	*/
	function getFilterOrderDir(){
		return  $this->getState('filter_order_Dir') ;
	}

	/**
	* Get all the invoice status available on the system
	* @return array An array of status values
	*/
	function getStatus(){

		if (empty( $this->status )){
			$this->status = InvoicesHelper::getStatus();

		}
		return $this->status;

	}

	function getCurrencyId(){
		if (empty($this->currency_id)) {
			$this->currency_id = $this->getState('currency_id')	;
		}
		return $this->currency_id;
	}

	/**
	* Build the ORDER BY clause for the DB clause
	* @return string The ORDER BY clause
	*/
	function _buildContentOrderBy()
	{

		$filter_order     = $this->getState('filter_order' ) ;
		$filter_order_Dir = $this->getState('filter_order_Dir') ;

		$orderby = ' ORDER BY '.$filter_order.' '.$filter_order_Dir . ' ';

		return $orderby;
	}

	/**
	* Builds the query to get the invoice list data from the DB
	* @return string The query itself
	*/
	function _buildQuery()
	{

		if(empty($this->query)){

			$db = JFactory::getDBO();

			$keywords = $this->getKeywords();
			$cal_start = $this->getCalstart();
			$cal_end = $this->getCalend();
			$status_id = $this->getStatusId();
			$currency_id = $this->getCurrencyId();

			$this->query = $db->getQuery(true);
			$this->query2 = $db->getQuery(true);
			$this->query3 = $db->getQuery(true);
			$this->query4 = $db->getQuery(true);
			$this->query5 = $db->getQuery(true);
			$this->query6 = $db->getQuery(true);

			if ($keywords != ""){

				$this->query->where('(' . $db->qn('i.invoice_num') . ' LIKE '. $db->q('%'.$keywords.'%')
											.' OR '. $db->qn('i.to_name') . ' LIKE '. $db->q('%'.$keywords.'%')
											.' OR '. $db->qn('i.to_company') . ' LIKE '. $db->q('%'.$keywords.'%')
											.' OR '. $db->qn('i.to_email') . ' LIKE '. $db->q('%'.$keywords.'%')
											.' OR '. $db->qn('i.to_address') . ' LIKE '. $db->q('%'.$keywords.'%')
											.' OR '. $db->qn('u.username') . ' LIKE '. $db->q('%'.$keywords.'%')
											.' OR '. $db->qn('co.name') . ' LIKE '. $db->q('%'.$keywords.'%').')');

				$this->query2 = clone $this->query;
				$this->query3 = clone $this->query;
				$this->query4 = clone $this->query;

			}

			if ($cal_start != ""){

				$this->query->where( $db->qn('i.invoice_date') . ' >= '. $db->q($cal_start) );
				$this->query2->where( $db->qn('pa.payment_datetime') . ' >= '. $db->q($cal_start) );
				$this->query3->where( $db->qn('i.invoice_date') . ' >= '. $db->q($cal_start) );
				$this->query4->where( $db->qn('i.invoice_date') . ' >= '. $db->q($cal_start) );
			}
			if ($cal_end != ""){

				$this->query->where( $db->qn('i.invoice_date') . ' <= '. $db->q($cal_end) );
				$this->query2->where( $db->qn('pa.payment_datetime') . ' <= '. $db->q($cal_end) );
				$this->query3->where( $db->qn('i.invoice_date') . ' <= '. $db->q($cal_end) );
				$this->query4->where( $db->qn('i.invoice_date') . ' <= '. $db->q($cal_end) );
			}
			if ($status_id != ""){

				$where_status_clause = '(' . $db->qn('i.status') . ' = '. $db->q($status_id) .' OR ('. $db->qn('i.computed_status') . ' = '. $db->q($status_id).' AND ' . $db->qn('i.status') . ' = "" ))';

				$this->query->where( $where_status_clause );
				$this->query2->where( $where_status_clause );
				$this->query3->where( $where_status_clause );
				$this->query4->where( $where_status_clause );
			}
			if (!empty($this->currency_id)) {

				$where_currency_id_clause = $db->qn('i.currency_id') . ' = '. $db->q($this->currency_id);

				$this->query->where( $where_currency_id_clause );
				$this->query2->where( $where_currency_id_clause );
				$this->query3->where( $where_currency_id_clause );
				$this->query4->where( $where_currency_id_clause );
			}
			if (!empty($this->external_type)) {

				$where_external_type_clause = $db->qn('i.external_type') . ' = '. $db->q($this->external_type);

				$this->query->where( $where_external_type_clause );
				$this->query2->where( $where_external_type_clause );
				$this->query3->where( $where_external_type_clause );
				$this->query4->where( $where_external_type_clause );
			}
			if (!empty($this->external_id)) {

				$where_external_id_clause = $db->qn('i.external_id') . ' = '. $db->q($this->external_id);

				$this->query->where( $where_external_id_clause );
				$this->query2->where( $where_external_id_clause );
				$this->query3->where( $where_external_id_clause );
				$this->query4->where( $where_external_id_clause );
			}
			if (!empty($this->external_ref)) {

				$where_external_ref_clause = $db->qn('i.external_ref') . ' = '. $db->q($this->external_ref);

				$this->query->where( $where_external_ref_clause );
				$this->query2->where( $where_external_ref_clause );
				$this->query3->where( $where_external_ref_clause );
				$this->query4->where( $where_external_ref_clause );
			}
			if (!empty($this->contact_id)) {

				$where_contact_id_clause = $db->qn('i.user_id') . ' = '. $db->q($this->contact_id);

				$this->query->where( $where_contact_id_clause );
				$this->query2->where( $where_contact_id_clause );
				$this->query3->where( $where_contact_id_clause );
				$this->query4->where( $where_contact_id_clause );
			}

			$type = $this->input->getInt('type', 1);

			$where_type_clause = $db->qn('i.type') . ' = '. $db->q($type);

			$this->query->where( $where_type_clause );
			$this->query2->where( $where_type_clause );
			$this->query3->where( $where_type_clause );
			$this->query4->where( $where_type_clause );

			$orderby = $this->_buildContentOrderBy();

			$this->query2->where( $db->qn('payment_status') . ' = 1 ' );
			$this->query3->where( '( ' . $db->qn('payment_status') . ' = 0 OR ' . $db->qn('payment_status') . ' = 2 ) ');

			$this->query5 = clone $this->query;

			$filter_order     = $this->getState('filter_order' ) ;
			$filter_order_Dir = $this->getState('filter_order_Dir') ;

			$this->query
						->select(array('i.*', $db->qn('u.name', 'username'), $db->qn('co.name', 'contact_name'), $db->qn('co.user_id', 'joomla_user_id')))
						->from($db->qn('#__invoices_invoices', 'i'))
						->leftJoin($db->qn('#__invoices_contacts', 'co') . ' ON (' . $db->qn('co.id') . ' = ' . $db->qn('i.user_id') . ')')
						->leftJoin($db->qn('#__users', 'u') . ' ON (' . $db->qn('u.id') . ' = ' . $db->qn('co.user_id') . ')')
						->order($db->qn($filter_order) . ' ' . $filter_order_Dir)
						;

			$this->query2
						->select(array('SUM(pa.payment_amount) AS total',  $db->qn('i.currency_id')))
						->from($db->qn('#__invoices_payments', 'pa'))
						->leftJoin($db->qn('#__invoices_invoices', 'i') . ' ON (' . $db->qn('i.id') . ' = ' . $db->qn('pa.invoice_id') . ')')
						->leftJoin($db->qn('#__invoices_contacts', 'co') . ' ON (' . $db->qn('co.id') . ' = ' . $db->qn('i.user_id') . ')')
						->leftJoin($db->qn('#__users', 'u') . ' ON (' . $db->qn('u.id') . ' = ' . $db->qn('co.user_id') . ')')
						;

			$this->query3
						->select(array('SUM(pa.payment_amount) AS total', $db->qn('i.currency_id')))
						->from($db->qn('#__invoices_payments', 'pa'))
						->leftJoin($db->qn('#__invoices_invoices', 'i') . ' ON (' . $db->qn('i.id') . ' = ' . $db->qn('pa.invoice_id') . ')')
						->leftJoin($db->qn('#__invoices_contacts', 'co') . ' ON (' . $db->qn('co.id') . ' = ' . $db->qn('i.user_id') . ')')
						->leftJoin($db->qn('#__users', 'u') . ' ON (' . $db->qn('u.id') . ' = ' . $db->qn('co.user_id') . ')')
						;

			$this->query4
						->select(array('SUM(i.computed_total) AS total', 'SUM(i.computed_subtotal) AS subtotal', $db->qn('i.currency_id')))
						->from($db->qn('#__invoices_invoices', 'i'))
						->leftJoin($db->qn('#__invoices_contacts', 'co') . ' ON (' . $db->qn('co.id') . ' = ' . $db->qn('i.user_id') . ')')
						->leftJoin($db->qn('#__users', 'u') . ' ON (' . $db->qn('u.id') . ' = ' . $db->qn('co.user_id') . ')')
						;

			$this->query6 = clone $this->query5;

			$this->query5
						->select(array('SUM(tai.computed_value) AS computed_value', $db->qn('tai.tax_id'), $db->qn('i.currency_id')))
						->from($db->qn('#__invoices_tax_invoice', 'tai'))
						->leftJoin($db->qn('#__invoices_invoices', 'i') . ' ON (' . $db->qn('i.id') . ' = ' . $db->qn('tai.reference_id') . ')')
						->leftJoin($db->qn('#__invoices_contacts', 'co') . ' ON (' . $db->qn('co.id') . ' = ' . $db->qn('i.user_id') . ')')
						->leftJoin($db->qn('#__users', 'u') . ' ON (' . $db->qn('u.id') . ' = ' . $db->qn('co.user_id') . ')')
						->where($db->qn('tai.active') . ' = 1 ')
						->where($db->qn('tai.type') . ' = 1 ')
						->group($db->qn('tai.tax_id'))
						->group($db->qn('i.currency_id'))

						;

			$this->query6
						->select(array('SUM((it.value * it.amount - it.discount)*(it.tax/100)) AS computed_value', $db->qn('it.tax_id'), $db->qn('i.currency_id')))
						->from($db->qn('#__invoices_items', 'it'))
						->leftJoin($db->qn('#__invoices_invoices', 'i') . ' ON (' . $db->qn('i.id') . ' = ' . $db->qn('it.invoice_id') . ')')
						->leftJoin($db->qn('#__invoices_contacts', 'co') . ' ON (' . $db->qn('co.id') . ' = ' . $db->qn('i.user_id') . ')')
						->leftJoin($db->qn('#__users', 'u') . ' ON (' . $db->qn('u.id') . ' = ' . $db->qn('co.user_id') . ')')
						->group($db->qn('it.tax_id'))
						->group($db->qn('i.currency_id'))
						;

			}

		return $this->query;
	}

	/**
	* Get the totals to display in bashboard boxes, based on the current query and filters
	* @return stdClass Object containing several total values, like num_invoices, total_income...
	*/
	function getTotals(){

		$params = JComponentHelper::getParams( 'com_invoices' );
		$db = JFactory::getDBO();

		$active_currencies = InvoicesHelper::getCurrencies();
		$default_currency_id = $params->get('currency_id', 1);

		$totals = new stdClass();

		$this->_buildQuery();

		//if we are working with multiple currency...
		if(true){
			$this->query2->group($db->qn('i.currency_id'));
			$this->query3->group($db->qn('i.currency_id'));
			$this->query4->group($db->qn('i.currency_id'));
		}

		$totals->num_invoices = $this->getTotal();
		if(!$totals->num_invoices) $totals->num_invoices = 0;

		$db->setQuery($this->query2);
 		$totals->total_income = $db->loadObjectList('currency_id');
 		//we group the totals with no currency with the default currency
 		if(isset($totals->total_income[0])) {
			if(!isset($totals->total_income[$default_currency_id])) $totals->total_income[$default_currency_id] = new stdClass();
 			$totals->total_income[$default_currency_id]->total += $totals->total_income[0]->total;
 			unset($totals->total_income[0]);
 		}

 		$db->setQuery($this->query3);
 		$totals->pending_income = $db->loadObjectList('currency_id');
 		//we group the totals with no currency with the default currency
 		if(isset($totals->pending_income[0])){
			if(!isset($totals->pending_income[$default_currency_id])) $totals->pending_income[$default_currency_id] = new stdClass();
 			$totals->pending_income[$default_currency_id]->total += $totals->pending_income[0]->total;
 			unset($totals->pending_income[0]);
 		}

 		$db->setQuery($this->query4);
 		$totals->total_invoices = $db->loadObjectList('currency_id');
 		//we group the totals with no currency with the default currency
 		if(isset($totals->total_invoices[0])){
			if(!isset($totals->total_invoices[$default_currency_id])) $totals->total_invoices[$default_currency_id] = new stdClass();
 			$totals->total_invoices[$default_currency_id]->total += $totals->total_invoices[0]->total;
			$totals->total_invoices[$default_currency_id]->subtotal += $totals->total_invoices[0]->subtotal;
 			unset($totals->total_invoices[0]);
 		}

		$query = ' SELECT * FROM #__invoices_taxes WHERE show_column = 1 ';
		$db->setQuery($query);
		$show_taxes = $db->loadObjectList('id');

		$db->setQuery($this->query5);
		$totals->global_taxes = $db->loadObjectList();
		if(!$totals->global_taxes) $totals->global_taxes = array();

		$db->setQuery($this->query6);
		$totals->items_taxes = $db->loadObjectList();
		if(!$totals->items_taxes) $totals->items_taxes = array();

		$organized_global_taxes = array();

		foreach ($totals->global_taxes as $globaltax) {
			if(!isset($organized_global_taxes[$globaltax->tax_id])) $organized_global_taxes[$globaltax->tax_id] = array();

			//if no currency is assigned, we assign it to the default one
			if(!$globaltax->currency_id) $globaltax->currency_id = $default_currency_id;

			$organized_global_taxes[$globaltax->tax_id][$globaltax->currency_id] += $globaltax->computed_value;
		}

		$totals->global_taxes = $organized_global_taxes;

		$organized_items_taxes = array();

		foreach ($totals->items_taxes as $itemtax) {
			if(!isset($organized_items_taxes[$itemtax->tax_id])) $organized_items_taxes[$itemtax->tax_id] = array();

			//if no currency is assigned, we assign it to the default one
			if(!$itemtax->currency_id) $itemtax->currency_id = $default_currency_id;

			$organized_items_taxes[$itemtax->tax_id][$itemtax->currency_id] += $itemtax->computed_value;
		}

		$totals->items_taxes = $organized_items_taxes;

		$organized_total_taxes = array();

		foreach($show_taxes as $tax_id => $thetax){
			if(isset($totals->global_taxes[$tax_id])) $global_tax = $totals->global_taxes[$tax_id]->computed_value;
			else $global_tax = 0 ;
			if(isset($totals->items_taxes[$tax_id])) $items_tax = $totals->items_taxes[$tax_id]->computed_value;
			else $items_tax = 0 ;

			$show_taxes[$tax_id]->computed_value = $global_tax + $items_tax;
		}

		foreach($show_taxes as $tax_id => $thetax){

			foreach($active_currencies as $currency_id => $currency){

				if(isset($totals->global_taxes[$tax_id][$currency_id])) $global_tax = $totals->global_taxes[$tax_id][$currency_id];
				else $global_tax = 0 ;
				if(isset($totals->items_taxes[$tax_id][$currency_id])) $items_tax = $totals->items_taxes[$tax_id][$currency_id];
				else $items_tax = 0 ;

				if(!isset($organized_total_taxes[$tax_id])) $organized_total_taxes[$tax_id] = array();

				$organized_total_taxes[$tax_id][$currency_id] = $global_tax + $items_tax;

			}

		}

		$totals->taxes = $organized_total_taxes;

		//print_r($totals);die;

		$timespan = "";

		if($this->cal_start) $timespan .= JText::_( 'FROM' ) ." ". JHTML::_('date', $this->cal_start, JText::_('DATE_FORMAT_PAYMENTS')) ;
		if($this->cal_end) $timespan .= " " . JText::_( 'TO' ) ." ". JHTML::_('date', $this->cal_end, JText::_('DATE_FORMAT_PAYMENTS')) ;
		if(!$this->cal_start && !$this->cal_end) $timespan .= JText::_( 'ALL_TIME' ) ;

		$totals->timespan = $timespan;

		$totals->update = 0;

		return $totals;
	}

	/**
	* Get the invoices for the current query
	* @return Array An array of invoices
	*/
	function getData(){

		$params = JComponentHelper::getParams( 'com_invoices' );

		if (empty( $this->_data )){
			$query = $this->_buildQuery();

			if($this->input->get('task') == "export") $this->_data = $this->_getList($query);
			else $this->_data = $this->_getList($query, $this->getState('limitstart'), $this->getState('limit'));

			for($i = 0; $i < count($this->_data) ; $i++){

				$row =& $this->_data[$i] ;

				//v2.2
				$query = 	' SELECT it.*, tax.name AS tax_name FROM #__invoices_items AS it '.
									' LEFT JOIN #__invoices_taxes AS tax ON tax.id = it.tax_id '.
									' WHERE it.invoice_id = ' . $row->id .
									' ORDER BY it.ordering ';
				$this->_db->setQuery( $query );
				$row->items = $this->_db->loadObjectList();

				$query = 	' SELECT * FROM #__invoices_payments '.
									' WHERE invoice_id = ' . $row->id .
									' ORDER BY ordering ';
				$this->_db->setQuery( $query );
				$row->payments = $this->_db->loadObjectList();

				$row->taxes = $this->getInvoiceTaxes($row->id);

				$row->computed = json_decode($row->computed);

				if(InvoicesHelper::allowEdit($row->id, $row->created_by)) $row->allow_edit = 1 ;
				else $row->allow_edit = 0 ;

				if($row->joomla_user_id) $row->username =  $row->username . " [".$row->joomla_user_id."]";
				else $row->username =  JText::_('JNO');

				switch($row->type){
					case 1: //invoice
					$row->is_quote = 0 ;
					$row->edit_link = JRoute::_('index.php?option=com_invoices&controller=invoice&tmpl=component&task=edit&cid[]=' . $row->id . '&layout=form&modal=1' , false);

					break;
					case 2: //quote
					$row->is_quote = 1 ;
					$row->edit_link = JRoute::_('index.php?option=com_invoices&controller=quote&tmpl=component&task=edit&cid[]=' . $row->id . '&layout=form&modal=1' , false);

					break;
				}

				$row->link 		= JRoute::_('index.php?option=com_invoices&controller=invoice&view=invoice&cid[]='.$row->id.'&tmpl=component', false);
				$row->link_contact 		= JRoute::_( 'index.php?option=com_invoices&controller=contact&task=edit&cid[]='. $row->user_id , false);
				$row->link_payments 	= JRoute::_( 'index.php?option=com_invoices&controller=payments&invoice_id='. $row->id , false);
				$row->link_new_payment 	= JRoute::_( 'index.php?option=com_invoices&controller=payment&task=edit&cid[]=0&invoice_id='. $row->id .'&from=invoices' , false);
				$row->pdf_link 			= InvoicesHelper::download_pdf_link($row->id);
				$row->email_link 		= InvoicesHelper::send_email_link($row->id) . "&ajax=1";
				$row->public_link 			= InvoicesHelper::getInvoicePublicLink($row->id);

				if(!$row->currency_id) $row->currency_id = $params->get('currency_id', 1);

				if($row->last_sent != "0000-00-00 00:00:00") {
					$row->sent = 1;
				}
				else {
					$row->sent = 0;
				}

			}

		}

		return $this->_data;

	}

	/**
	* Get an array of invoice IDs that contain items with a name matching the keywords
	* @param  string $keywords The wildcard to look for in items names
	* @return array           An array of invoice IDs
	*/
	private function getInvoiceIdOfItem($keywords){
		$query = ' SELECT i.invoice_id FROM #__invoices_items AS i WHERE i.name LIKE "%'.$keywords.'%" ';
		$this->_db->setQuery($query);

		$query_result = $this->_db->loadAssocList();
		$result = array();
		foreach($query_result as $k=>$v) {
			$result[$k] = $v['invoice_id'];
		}
		return $result;
	}

	function getInvoiceTaxes($id){

		if($id){ //is not new
			$query = 	' SELECT tax.*, tai.value AS tax_value, tai.active FROM #__invoices_taxes AS tax '
						.' LEFT JOIN #__invoices_tax_invoice AS tai ON (tai.tax_id = tax.id AND tai.reference_id = ' . $id . ' AND tai.type = 1) '
						.' ORDER BY ordering, name ';
		}
		else{ //is new
			$query = 	' SELECT tax.*, tax.value AS tax_value, tax.checked AS active FROM #__invoices_taxes AS tax '
							.' ORDER BY ordering, name ';
		}
		$this->_db->setQuery( $query );
		$taxes = $this->_db->loadObjectList('id');

		return $taxes;

	}

	function getDataGrouped(){

		if (empty( $grouped )){

			$eu = $this->getData();

			$grouped = array();

			foreach($eu as $invoice){
				if($invoice->taxes[1]->active) $tax_percentage = $invoice->taxes[1]->tax_value;
				else $tax_percentage = 0 ;

				$grouped[$invoice->to_country][$tax_percentage] += InvoicesHelper::get_subtotal($invoice->id) ;
			}

		}

		uksort($grouped, "cmp");

 		return $grouped;

	}

}

function cmp($a, $b)
{

	$array_countries = array("Austria", "Belgium", "Bulgaria", "Cyprus", "Czech Republic", "Croatia", "Denmark", "Estonia", "Finland", "France", "Germany",
		"Greece", "Hungary", "Ireland", "Italy", "Latvia", "Lithuania", "Luxembourg", "Malta", "Netherlands", "Poland", "Portugal", "Azores",
		"Romania", "Slovakia", "Slovenia", "Spain", "Sweden", "United Kingdom");

	if(in_array($a, $array_countries) && !in_array($b, $array_countries)) {

		return -1;}
	elseif(!in_array($a, $array_countries) && in_array($b, $array_countries)) {

		return 1;}

	else {

		return strcmp($a, $b);

	}

}
