order by value from callback function?

[marekmarek123]

Hi,

I would like to sort by values from callback function but it doesn't work because I can't get values directly from mysql select function. Can you help me, if it is possible to do this, or I have to change everything and try to join tables and use just one sql query?

 

I'm writing about printSelling callback function in sprzedaz field.

public function __construct()
	{
		$this->bootstrap = true;
		$this->table = 'product';
		$this->className = 'Product';
		$this->display = 'Product location';
		$this->meta_title = $this->l('Product location');
		$this->no_link = false;
		$this->context = Context::getContext();
        $shop=$this->context->cookie->shopContext;
        $id_shop = (int)str_replace('s-','',$shop);
		$this->_select = "a.*, pl.name, sta.quantity as stock,s.id_supplier, a.id_product as sprzedaz, psh.id_shop, psh.id_tax_rules_group, pa.ean13, a.id_product as rezerwacja, cs.conversion_rate, pi.date_add as inventory, pa.id_product_attribute, a.id_product as id_newproduct_product, ps.product_supplier_price_te as buying_price,ps.id_currency,  IFNULL((psh.price+pa.price-ps.product_supplier_price_te*cs.conversion_rate)/(psh.price+pa.price),1) as magirn, s.name as supplier_name, a.active";
		//$this->_select = "a.*, pl.name, sta.quantity as stock,s.id_supplier, psh.id_shop,psh.id_tax_rules_group, pa.ean13, a.id_product as rezerwacja, cs.conversion_rate, pi.date_add as inventory, pa.id_product_attribute, a.id_product as id_newproduct_product, ps.product_supplier_price_te as buying_price,ps.id_currency,  IFNULL((psh.price+pa.price-ps.product_supplier_price_te*cs.conversion_rate)/(psh.price+pa.price),1) as magirn, s.name as supplier_name, a.active";
		$this->_where = ' AND pl.id_lang = '.$this->context->language->id.' '.($id_shop? 'AND psh.id_shop='.(int)$id_shop.' ':'');
		$this->_join = 'LEFT JOIN '._DB_PREFIX_.'product_attribute pa ON a.id_product = pa.id_product 
        LEFT JOIN '._DB_PREFIX_.'stock_available sta on (sta.id_product=pa.id_product AND sta.id_product_attribute=pa.id_product_attribute)
		LEFT JOIN '._DB_PREFIX_.'product_lang pl ON a.id_product = pl.id_product
        LEFT JOIN '._DB_PREFIX_.'product_shop psh ON a.id_product = psh.id_product 
        LEFT JOIN '._DB_PREFIX_.'product_supplier ps ON a.id_product=ps.id_product and pa.id_product_attribute=ps.id_product_attribute
        LEFT JOIN '._DB_PREFIX_.'product_inventory pi ON a.id_product=pi.id_product AND pa.id_product_attribute=pi.id_product_attribute
        lEFT JOIN '._DB_PREFIX_.'supplier s ON ps.id_supplier = s.id_supplier
		LEFT JOIN '._DB_PREFIX_.'image i ON a.id_product = i.id_product
		LEFT JOIN '._DB_PREFIX_.'warehouse_product_location ploc ON ploc.id_product = a.id_product
        INNER JOIN '._DB_PREFIX_.'currency_shop cs ON psh.id_shop = cs.id_shop AND ps.id_currency = cs.id_currency';
		$this->_group = 'GROUP BY a.id_product, pa.id_product_attribute,s.id_supplier,psh.id_shop';
		$this->_defaultOrderBy = 'magirn'; 
		$this->_defaultOrderWay='desc';
		$this->fields_list = array(
            'id_product' => array(
				'title' => $this->l('ID'),
				'align' => 'center',
				'class' => 'fixed-width-xs',
				'type' => 'int',
				
			),
			'id_product_attribute' => array(
				'title' => $this->l('ID PRODUCT ATTRIBUTE'),
				'align' => 'center',
				'class' => 'fixed-width-xs',
				'type' => 'int',
				
			),
            'supplier_name'=> array(
                'title' =>$this->l('Supplier name'),
                'type' =>'text'
            ),
            'id_shop' =>array(
                'title' => $this->l('id_shop'),
                'type' =>'text'
            ),
			'ean13' => array(
				'title' => $this->l('EAN'),
				'align' => 'center',
				'class' => 'fixed-width-xs',
				'type' => 'text',
				
			),
			'sprzedaz' => array(
				'title' => $this->l('Sprz (30 days)'),
				'align' => 'center',
				'type' => 'text',
				'callback' => 'printSelling',
			),
                
			'rezerwacja' => array(
				'title' => $this->l('Rez'),
				'align' => 'center',
				'type' => 'text',
				'callback' => 'printReservation',
			),
			'stock' => array(
				'title' => $this->l('Stock'),
				'align' => 'center',
				'type' => 'int',
				'callback' => 'printStock',
			),
			'location' => array(
				'title' => $this->l('Location'),
				'align' => 'center',
				'type' => 'text',
				'callback' => 'printLocation',
			),
			'name' => array(
				'title' => $this->l('Name'),
				'type' => 'text',
				'callback' => 'printName',
			),
			'inventory' => array(
				'title' => $this->l('Last inventory'),
				'type' => 'text',
			),
            'price' => array(
                'title' =>$this->l('Selling price'),
                'type' =>'input',
                'callback' => 'printPrice',
            ),
            'magirn' =>array(
                'title' => $this->l('Margin'),
                'type' => 'text',
                'callback' =>'printMargin',
            ),
			'active' => array(
				'title' => $this->l('Active'),
				'filter_key' => 'a!active',
				'active' => 'status',
				'align' => 'text-center',
				'type' => 'bool',
				'class' => 'fixed-width-sm',
			),
            
		);
		parent::__construct();
        
	}




public function printSelling($a, $
	{

		
			$datanow = date("Y-m-d H:i:s");   
			
			if(!isset($b['id_product_attribute']))
				$b['id_product_attribute'] = 0;
			
			$id_warehouse = 2;
			
			$id_stock_array = Db::getInstance()->executeS("SELECT id_stock FROM "._DB_PREFIX_."stock WHERE id_product = ".$b['id_product']." AND id_product_attribute = ".$b['id_product_attribute']." AND id_warehouse = ".$id_warehouse." ORDER BY id_stock");
			
			
			if (empty($id_stock_array)) return "--";
			
			$i = 0;
			$last = count($id_stock_array);
			$id_stocks_text = "";
			$id_stocks_text_id = "";
			foreach($id_stock_array as $id_stock){
				$id_stocks_text .= " psmafter.id_stock = ".$id_stock['id_stock'];
				$id_stocks_text_id .= " id_stock = ".$id_stock['id_stock'];
				$i++;
				if ($i < $last) {$id_stocks_text .= " OR ";$id_stocks_text_id .= " OR ";}
			}
			
			$sql = "SELECT psmnow.id_stock_mvt, psmnow.id_stock_mvt_reason, psmnow.physical_quantity, psmnow.sign, psmnow.date_add, (select psmafter.date_add from ps_stock_mvt psmafter where (".$id_stocks_text.") and psmafter.id_stock_mvt > psmnow.id_stock_mvt order by psmafter.id_stock_mvt ASC limit 1) as date_after FROM ps_stock_mvt psmnow where (".$id_stocks_text_id.") ORDER BY date_add, id_stock_mvt ASC";
			
			$result = Db::getInstance()->executeS($sql);

			$i = 0;
			foreach($result as &$element){
				$prev = $i-1;
				if ($element['id_stock_mvt_reason'] == 15) {
					$element['suma'] = $element['physical_quantity'];
				} else {
					if ($i == 0) $element['suma'] = $element['physical_quantity'] * $element['sign'];
					else {
						$element['suma'] = $result[$prev]['suma'] + $element['physical_quantity'] * $element['sign'];
					}
				}
				
				if ($element['date_after'] == NULL) $element['date_after'] = $datanow;
				
				if ($element['suma'] > 0) $element['liczba_dni'] = ceil((strtotime($element['date_after']) - strtotime($element['date_add'])) / (60*60*24));
				else $element['liczba_dni'] = 0;
				$i++;
			}
			
			unset($element);
			$wynik = array_reverse($result);
			$liczbaDniTmp = 0;
			$dataStart = $datanow;
			
			foreach ($wynik as $element)
			{
				$liczbaDniTmp += $element['liczba_dni'];
				$dataStart = $element['date_add'];
				if ($liczbaDniTmp > 30) break;
			}
			$roznica = $liczbaDniTmp - 30;
			
			if ($roznica > 0) $dataStart = date("Y-m-d H:i:s", strtotime("+".$roznica." days", strtotime($dataStart)));
			
			
			$sql = 'SELECT SUM(od.product_quantity) as qty,COUNT(o.id_order) as total_order FROM '._DB_PREFIX_.'order_detail od 
			LEFT JOIN '._DB_PREFIX_.'orders o ON od.id_order = o.id_order 
			WHERE od.product_id = '.$b['id_product'].' AND od.product_attribute_id = '.$b['id_product_attribute'].' AND o.date_add > "'.$dataStart.'" AND current_state IN(20,25)';
			$result = Db::getInstance()->executeS($sql);
			
			if($result[0]['qty'])
			  return $result[0]['qty'].'('.$result[0]['total_order'].')';
			
			return '--';
			
		
	}

Edited August 10, 2016 by marekmarek123 (see edit history)