Community Forums

[HOW TO] Add SKU to Invoices

Free manual modifications can be contributed here. Modifications are manual snippets of code that are pasted into the forums for others to use.

[HOW TO] Add SKU to Invoices

Postby H2O » Wed Mar 28, 2012 6:14 pm

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:
viewtopic.php?f=20&t=47839#p227588
viewtopic.php?f=20&t=16890&p=90878#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.
A good site for e-commerce advice.
H2O
 
Posts: 25
Joined: Tue Feb 28, 2012 6:36 pm

Re: [HOW TO] Add SKU to Invoices

Postby Deceiver » Mon Jun 11, 2012 2:07 am

Will this mod work with 1.5.3.1?
Deceiver
 
Posts: 4
Joined: Sun May 27, 2012 8:25 am

Re: [HOW TO] Add SKU to Invoices

Postby nosecret » Sat Jun 23, 2012 3:50 am

I have extension SKU in INVOICE to replace model in INVOICE.
nosecret
 
Posts: 240
Joined: Mon Dec 27, 2010 11:28 pm

Re: [HOW TO] Add SKU to Invoices

Postby websitedesignbristol » Tue Jul 17, 2012 1:21 pm

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
websitedesignbristol
 
Posts: 29
Joined: Wed Apr 25, 2012 4:39 am

Re: Whats the procedure for version 1.5.3.1

Postby chetanas77 » Thu Nov 22, 2012 12:07 am

I am using version 1.5.3.1. Whats the procedure to display sku in invoice.
Kindly reply..
chetanas77
 
Posts: 2
Joined: Tue Oct 30, 2012 12:41 am

Re: [HOW TO] Add SKU to Invoices

Postby MikeSCC » Tue Nov 27, 2012 10:45 am

Hello,

Please could somebody advise if this works with version 1.5.4?

Thank you,

Mike
MikeSCC
 
Posts: 111
Joined: Tue Nov 20, 2012 4:55 am

Re: [HOW TO] Add SKU to Invoices

Postby anssinda » Wed Jul 31, 2013 3:45 am

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.
anssinda
 
Posts: 1
Joined: Wed Jun 19, 2013 1:25 am

Re: [HOW TO] Add SKU to Invoices

Postby krneki » Tue Nov 05, 2013 7:28 am

Any updates for v1.5.6?
krneki
 
Posts: 137
Joined: Tue Jul 17, 2012 8:58 am

Re: [HOW TO] Add SKU to Invoices

Postby bedo02 » Sat Jan 11, 2014 12:10 pm

Hi,
I have written a new howto for 1.5.6
viewtopic.php?f=20&t=47839&p=467219#p467219

enjoy.
using oc 1.5.6
bedo02
 
Posts: 11
Joined: Mon Oct 26, 2009 8:43 am


Return to Modifications

Who is online

Users browsing this forum: No registered users and 7 guests

Hosted by Arvixe Web Hosting