OpenCart Community • [HOW TO] Add SKU to Invoices

Page 1 of 1

[HOW TO] Add SKU to Invoices

Posted: Thu Mar 29, 2012 7:14 am

by H2O

This Mod was done on version 1.5.2.1., and makes product SKUs appear on the printed invoice in a column after the Model column.

Most of the steps were taken from posts in two other threads, so the code is courtesy of them:
http://forum.opencart.com/viewtopic.php ... 39#p227588
http://forum.opencart.com/viewtopic.php ... 878#p90940

Disclaimer: These are the Mods I made, and it works great. I'm not even sure ALL these Mods are necessary to accomplish adding the SKUs. Someone who knows this stuff better than me may be able to cut out some of the changes if they weren't needed.

Please read whole tutorial before starting!

If you don't have a php editor, I'd suggest downloading Notepad++. It's free, and makes adding by line number easy.
http://notepad-plus-plus.org/

First we will be adding a column to the database as described. I'm not sure if this is necessary, but the changes we are going to make to some of the files below will pop errors because they refer to this :

1) add the table row for "order_product"

MySQL edit :

go to phpMyAdmin or whatever you are using, go to "your_opencart_db" and open the "order_product" table (may be called otherwise if you are using prefixes) and execute the following SQL query

Code: Select all
ALTER TABLE order_product ADD sku VARCHAR(60);

the new row named "sku" should appear.

Note : existing orders need to be populated with skus manually. Best way is to export a CSV File

of "order_product" table and "product" table and use vlookup function to match Product IDs

with SKUs and reupload. For troubleshooting see -> SQL Alter Table
http://www.tech-recipes.com/rx/378/add- ... sql-table/

We will be modifying five Opencart files:
admin/view/templates/sale/order_invoice.tpl
admin/language/english/sale/order.php
admin/model/sale/order.php
admin/controller/sale/order.php
catalog/model/checkout/order.php

You might just have to make the changes to the bold files above to add the SKUs to the invoice. Try making those changes first and test before changing the database and doing the last two.

backup all of the files before you change them!

starting with:
admin/view/templates/sale/order_invoice.tpl

around line 67, look for:

Code: Select all

<td><b><?php echo $column_model; ?></b></td>

below it add:

Code: Select all

<td><b><?php echo $column_sku; ?></b></td>

then around line 79, look for:

Code: Select all

<td><?php echo $product['model']; ?></td>

and below it add:

Code: Select all

<td align="right"><?php echo $product['sku']; ?></td>

Around line 97, look for

Code: Select all

<td align="right" colspan="4"><b><?php echo $total['title']; ?>:</b></td>

REPLACE it with:

Code: Select all

<td align="right" colspan="5"><b><?php echo $total['title']; ?>:</b></td>

and we are done with that file.

Now move onto:
admin/language/english/sale/order.php

around line 125, under the "// Column" section, add:

Code: Select all

$_['column_sku']                              = 'SKU';

and save and close that file.

Now we're going to open:
admin/model/sale/order.php
-------------------------- These may error if you didn't add column to the database -------------------------
Around line 84, look for:

Code: Select all

if (isset($data['order_product'])) {		
      		foreach ($data['order_product'] as $order_product) {	
      			$this->db->query("INSERT INTO " . DB_PREFIX . "order_product SET order_id = '" . (int)$order_id . "', product_id = '" . (int)$order_product['product_id'] . "', name = '" . $this->db->escape($order_product['name']) . "', model = '" . $this->db->escape($order_product['model']) . "', quantity = '" . (int)$order_product['quantity'] . "', price = '" . (float)$order_product['price'] . "', total = '" . (float)$order_product['total'] . "', tax = '" . (float)$order_product['tax'] . "', reward = '" . (int)$order_product['reward'] . "'");

REPLACE with:

Code: Select all

if (isset($data['order_product'])) {		
      		foreach ($data['order_product'] as $order_product) {	
      			$this->db->query("INSERT INTO " . DB_PREFIX . "order_product SET order_id = '" . (int)$order_id . "', product_id = '" . (int)$order_product['product_id'] . "', name = '" . $this->db->escape($order_product['name']) . "', model = '" . $this->db->escape($order_product['model']) . "', quantity = '" . (int)$order_product['quantity'] . "', price = '" . (float)$order_product['price'] . "', total = '" . (float)$order_product['total'] . "', tax = '" . (float)$order_product['tax'] . "', reward = '" . (int)$order_product['reward'] . "', sku = '" . (float)$product['sku'] . "'");

Around line 174, find:

Code: Select all

foreach ($data['order_product'] as $order_product) {	
      			$this->db->query("INSERT INTO " . DB_PREFIX . "order_product SET order_product_id = '" . (int)$order_product['order_product_id'] . "', order_id = '" . (int)$order_id . "', product_id = '" . (int)$order_product['product_id'] . "', name = '" . $this->db->escape($order_product['name']) . "', model = '" . $this->db->escape($order_product['model']) . "', quantity = '" . (int)$order_product['quantity'] . "', price = '" . (float)$order_product['price'] . "', total = '" . (float)$order_product['total'] . "', tax = '" . (float)$order_product['tax'] . "', reward = '" . (int)$order_product['reward'] . "'");

REPLACE with:

Code: Select all

foreach ($data['order_product'] as $order_product) {	
      			$this->db->query("INSERT INTO " . DB_PREFIX . "order_product SET order_product_id = '" . (int)$order_product['order_product_id'] . "', order_id = '" . (int)$order_id . "', product_id = '" . (int)$order_product['product_id'] . "', name = '" . $this->db->escape($order_product['name']) . "', model = '" . $this->db->escape($order_product['model']) . "', quantity = '" . (int)$order_product['quantity'] . "', price = '" . (float)$order_product['price'] . "', total = '" . (float)$order_product['total'] . "', tax = '" . (float)$order_product['tax'] . "', reward = '" . (int)$order_product['reward'] . "' , sku = '" . (float)$product['sku'] . "'");

-----------------------------------------------------------------------------------------------------------------------

Look around line 468 for:

Code: Select all

 public function getOrderProducts($order_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "order_product WHERE order_id = '" . (int)$order_id . "'");
		
		return $query->rows;

REPLACE it with:

Code: Select all

public function getOrderProducts($order_id) {
      $query = $this->db->query("SELECT a.*, b.sku FROM " . DB_PREFIX . "order_product a LEFT JOIN " . DB_PREFIX . "product b ON a.product_id = b.product_id WHERE order_id = '" . (int)$order_id . "'");
     
      return $query->rows;
   }

and save and close that file.

This step MAY not be needed. But will pop errors if you didn't add the column to the database as described above. Next we open:
admin/controller/sale/order.php

At line number 1089, look for:

Code: Select all

'reward'           => $order_product['reward'],

and below it add:

Code: Select all

'sku'              => $order_product['sku']

around line 2284, look for:

Code: Select all

$this->data['column_model'] = $this->language->get('column_model');

and below it add:

Code: Select all

$this->data['column_sku'] = $this->language->get('column_sku');

then around line 2422, look for:

Code: Select all

'option'   => $option_data,

and below it add:

Code: Select all

'sku'      => $product['sku'],

and save and close that file.

This step MAY not be needed. But will pop errors if you didn't add the column to the database as described above.Now we are going to modify:
catalog/model/checkout/order.php

Around line 8, look for:

Code: Select all

foreach ($data['products'] as $product) { 
			$this->db->query("INSERT INTO " . DB_PREFIX . "order_product SET order_id = '" . (int)$order_id . "', product_id = '" . (int)$product['product_id'] . "', name = '" . $this->db->escape($product['name']) . "', model = '" . $this->db->escape($product['model']) . "', quantity = '" . (int)$product['quantity'] . "', price = '" . (float)$product['price'] . "', total = '" . (float)$product['total'] . "', tax = '" . (float)$product['tax'] . "', reward = '" . (int)$product['reward'] . "''");

REPLACE it with:

Code: Select all

foreach ($data['products'] as $product) { 
			$this->db->query("INSERT INTO " . DB_PREFIX . "order_product SET order_id = '" . (int)$order_id . "', product_id = '" . (int)$product['product_id'] . "', name = '" . $this->db->escape($product['name']) . "', model = '" . $this->db->escape($product['model']) . "', quantity = '" . (int)$product['quantity'] . "', price = '" . (float)$product['price'] . "', total = '" . (float)$product['total'] . "', tax = '" . (float)$product['tax'] . "', reward = '" . (int)$product['reward'] . "', sku = '" . (float)$product['sku'] . "'");

and save and close that file.

I'm not sure ALL the steps are needed, but can say that if you do all the steps, it WILL work!

You should now have SKU numbers added to the invoice.

Someone please let me know if they were successful in adding the SKUs by just changing the first three files.

Re: [HOW TO] Add SKU to Invoices

Posted: Mon Jun 11, 2012 3:07 pm

by Deceiver

Will this mod work with 1.5.3.1?

Re: [HOW TO] Add SKU to Invoices

Posted: Sat Jun 23, 2012 4:50 pm

by nosecret

I have extension SKU in INVOICE to replace model in INVOICE.

Re: [HOW TO] Add SKU to Invoices

Posted: Wed Jul 18, 2012 2:21 am

by websitedesignbristol

ive done all of the above and checked it numerous times.
i am getting this error message.
Notice: Undefined index: sku in /home3/tigermis/public_html/catalog/model/checkout/order.php on line 9
is there anyway to over come this or any ideas whats wrong?
website address is www.tigermist.com

Re: Whats the procedure for version 1.5.3.1

Posted: Thu Nov 22, 2012 1:07 pm

by chetanas77

I am using version 1.5.3.1. Whats the procedure to display sku in invoice.
Kindly reply..

Re: [HOW TO] Add SKU to Invoices

Posted: Tue Nov 27, 2012 11:45 pm

by MikeSCC

Hello,

Please could somebody advise if this works with version 1.5.4?

Thank you,

Mike

Re: [HOW TO] Add SKU to Invoices

Posted: Wed Jul 31, 2013 4:45 pm

by anssinda

Hello,

I did this modification for OC 1.5.5.1. It seems to work, just forget the last file catalog/model/checkout/order.php. It should not be modified, otherwise there will be "Undifined index..." error that websitedesignbristol mentioned.

Line numbers are slightly different, but otherwise the instruction above works.

Re: [HOW TO] Add SKU to Invoices

Posted: Tue Nov 05, 2013 8:28 pm

by krneki

Any updates for v1.5.6?

Re: [HOW TO] Add SKU to Invoices

Posted: Sun Jan 12, 2014 1:10 am

by bedo02

Hi,
I have written a new howto for 1.5.6
http://forum.opencart.com/viewtopic.php ... 19#p467219

enjoy.

Re: [HOW TO] Add SKU to Invoices

Posted: Fri Mar 03, 2017 12:14 am

by salwa123

are you work in oc 2.3.0.2