Transaction Records Module

[huqike]

Hi I followed the dev documentation and create a transaction records module, it shows name (first letter is replaced by '*'), price, quantity, purchase date, attributes. It's designed for my customized template and may need to do additional coding for yours by editing hookproducttab.tpl and transactionrecords.tpl.
 
What I did is basically here:
 

public function hookProductTabContent($params)
{
	$id_product = (int)(Tools::getValue('id_product'));
			$sql = 'SELECT p.`id_product`,
				od.`id_order_detail`,
				substring(c.`firstname`,2) AS firstname,
				CHAR_LENGTH(od.`product_name`)-CHAR_LENGTH(pl.`name`) AS issetattr,
				RIGHT(od.`product_name`,CHAR_LENGTH(od.`product_name`)-CHAR_LENGTH(pl.`name`)-2) AS product_attribute,
				LEFT(od.`unit_price_tax_incl`,LENGTH(od.`unit_price_tax_incl`)-7) AS unit_price,
				od.`product_quantity` AS product_quantity,
				oh.`date_add` AS date_add
			FROM `'._DB_PREFIX_.'product` p
				LEFT JOIN `'._DB_PREFIX_.'order_detail` od ON (p.`id_product` = od.`product_id`) 
				LEFT JOIN `'._DB_PREFIX_.'product_lang` pl ON (p.`id_product` = pl.`id_product`)
				LEFT JOIN `'._DB_PREFIX_.'orders` o ON (od.`id_order` = o.`id_order`) 
				LEFT JOIN `'._DB_PREFIX_.'order_history` oh ON (o.`id_order` = oh.`id_order`)
				LEFT JOIN `'._DB_PREFIX_.'customer` c ON (o.`id_customer` = c.`id_customer`)
			WHERE oh.id_order_state = 2 AND pl.id_lang = 1 AND p.id_product = '.$id_product.'
			ORDER BY oh.date_add DESC';
	
	$transactionrecords = Db::getInstance()->ExecuteS($sql);
	$getrownumber = Db::getInstance()->NumRows($transactionrecords);		
		$this->context->smarty->assign(
			array(
			  'transaction_records' => Configuration::get('TRANSACTIONRECORDS_SETTING'),
			  'transaction_records_link' => $this->context->link->getModuleLink('transactionrecords','display'),
			  'records' => $transactionrecords,
			  'rnum' => $getrownumber

			)
		);
  return $this->display(__FILE__, 'transactionrecords.tpl');
}

You may wanna change "oh.id_order_state = 2 AND pl.id_lang = 1" to your case.

 

FYI: there's no pagination and I don't know how to do it. If anyone can make it and make the module more efficient please share yours, which would be very appreciated.

 
Example from my site:

 
Module:
transactionrecords.zip

Edited May 27, 2015 by huqike (see edit history)