Page 1 of 2
Adding the category to invoices
Posted: Tue Nov 15, 2011 6:17 am
by mkc
Any idea how to do it? I modded the order-invoice.tpl to look the way I want, but where/how would I add the category that the item is in? I'd even like to add the manufacturer if possible.
Anyone have experience in this realm? I can code PHP, I just need to be able to find the source of the invoice and can probably hastily add what I'd like.
Re: Adding the category to invoices
Posted: Mon Nov 21, 2011 11:56 am
by mkc
Anyone? Anything? Very happening place...
Re: Adding the category to invoices
Posted: Thu Nov 24, 2011 9:50 am
by straightlight
Simply by reversing psychology, when we can't get a product by categories, we have to get a category by products.
In admin/controller/sale/order.php file,
Find:
Code: Select all
$this->data['column_model'] = $this->language->get('column_model');
add above:
Code: Select all
$this->data['column_category'] = $this->language->get('column_category');
Then, find:
add above:
Code: Select all
$category_info = $this->model_catalog_product->getCategoriesByProduct($order_product['product_id']);
if ($category_info) {
$this->data['category_info'] = array();
foreach ($category_info as $cat_info) {
$this->data['category_info'][] = array(
'product_id' => (int)$cat_info['product_id'],
'name' => $cat_info['name'],
);
}
}
In admin/model/catalog/product.php file,
Find:
Code: Select all
public function getProductsByCategoryId($category_id) {
$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (p.product_id = p2c.product_id) WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2c.category_id = '" . (int)$category_id . "' ORDER BY pd.name ASC");
return $query->rows;
}
Add above:
Code: Select all
public function getCategoriesByProduct($product_id) {
$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "category c LEFT JOIN " . DB_PREFIX . "category_description cd ON (cd.category_id = c.product_id) LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (c.category_id = p2c.category_id) WHERE cd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2c.product_id = '" . (int)$product_id . "' ORDER BY pd.name ASC");
return $query->rows;
}
Of course, this is a quick way of doing it while a separate model file could be built in order to adapt it into the controller file.
In admin/view/template/sale/order_form.tpl file,
find:
Code: Select all
<td class="left"><?php echo $column_model; ?></td>
Add above:
Code: Select all
<td class="left"><?php echo $column_category; ?></td>
Then, find:
Code: Select all
<td class="left"><?php echo $order_product['name']; ?><br />
add below (
NOT above):
Code: Select all
<?php foreach ($category_info as $cat_info) { ?>
<?php if ((int)$order_product['product_id'] == (int)$cat_info['product_id']) { ?>
<td class="left"><?php echo $cat_info['name']; ?><br />
<?php } ?>
<?php } ?>
In admin/language/english/sale/order.php file,
find:
add above:
Code: Select all
$_['column_category'] = 'Categories';
All set.
Re: Adding the category to invoices
Posted: Mon Jan 07, 2013 9:20 pm
by harryo40
@straightlight,
I too am after something like this were I can see the category in an extra column of the sales >> orders >> edit page but nothing showing?
I have the extra column displaying but nothing shows in the category box of each product?
Here's what I have done so far...
In admin/controller/sale/order.php file, I have added...
Code: Select all
$this->data['column_category'] = $this->language->get('column_category');
& also added...
Code: Select all
$category_info = $this->model_catalog_product->getCategoriesByProduct($order_product['product_id']);
if ($category_info) {
$this->data['category_info'] = array();
foreach ($category_info as $cat_info) {
$this->data['category_info'][] = array(
'product_id' => (int)$cat_info['product_id'],
'name' => $cat_info['name'],
);
}
}
In admin/model/catalog/product.php file, I have added...
Code: Select all
public function getCategoriesByProduct($product_id) {
$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "category c LEFT JOIN " . DB_PREFIX . "category_description cd ON (cd.category_id = c.product_id) LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (c.category_id = p2c.category_id) WHERE cd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2c.product_id = '" . (int)$product_id . "' ORDER BY pd.name ASC");
return $query->rows;
}
In admin/view/template/sale/order_form.tpl, I have added...
Code: Select all
<td class="left"><?php echo $column_category; ?></td>
& also added...
Code: Select all
<?php foreach ($category_info as $cat_info) { ?>
<?php if ((int)$order_product['product_id'] == (int)$cat_info['product_id']) { ?>
<td class="left"><?php echo $cat_info['name']; ?><br />
<?php } ?>
<?php } ?>
In admin/language/english/sale/order.php file, I have added...
Code: Select all
$_['column_category'] = 'Categories';
The word 'Categories' shows at the top of the column but nothing else?
thanks for any help
Re: Adding the category to invoices
Posted: Mon Jan 07, 2013 9:51 pm
by straightlight
From the instructions above, replace:
Find:
Code: Select all
$category_info = $this->model_catalog_product->getCategoriesByProduct($order_product['product_id']);
Replace with:
Code: Select all
if ($order_info) {
$category_info = $this->model_catalog_product->getCategoriesByProduct($order_product['product_id'], $order_info['store_id']);
} else {
$category_info = $this->model_catalog_product->getCategoriesByProduct($order_product['product_id'], $this->config->get('config_store_id'));
}
Then, find:
Code: Select all
public function getCategoriesByProduct($product_id) {
$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "category c LEFT JOIN " . DB_PREFIX . "category_description cd ON (cd.category_id = c.product_id) LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (c.category_id = p2c.category_id) WHERE cd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2c.product_id = '" . (int)$product_id . "' ORDER BY pd.name ASC");
return $query->rows;
}
replace with:
Code: Select all
public function getCategoriesByProduct($product_id, $store_id) {
$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "category c LEFT JOIN " . DB_PREFIX . "category_description cd ON (cd.category_id = c.category_id) LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (p2c.category_id = cd.category_id) LEFT JOIN `" . DB_PREFIX . "category_to_store` c2s ON (c2s.`category_id` = p2c.`category_id`) WHERE cd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2c.product_id = '" . (int)$product_id . "' AND c2s.`store_id` = '" . (int)$store_id . "' ORDER BY pd.name ASC");
return $query->rows;
}
This should correct the problem.
Re: Adding the category to invoices
Posted: Mon Jan 07, 2013 10:34 pm
by harryo40
Thanks for the speedy response but still something isnt right
I have since edited the exact named files being...
admin/controller/sale/order.php
admin/model/catalog/product.php
admin/view/template/sale/order_form.tpl
admin/language/english/sale/order.php
but no column shows or nothing & from the changes that have been made to OC1.5.x, I think I should be editing the admin/view/template/sale/
order_info.tpl instead of the .../sale/
order_form.tpl but I have tried editing the order_info.tpl & I now have the column showing but with no categories
Re: Adding the category to invoices
Posted: Mon Jan 07, 2013 11:03 pm
by harryo40
Just a small note to add & Im using vqmod to do this so I have made the latest recommended changes then checked the vqmod cache files & all parts have been created in all 4 files.
If I put this line of code in mysql database...
Code: Select all
SELECT cd.name FROM category c LEFT JOIN category_description cd ON (cd.category_id = c.category_id) LEFT JOIN product_to_category p2c ON (p2c.category_id = cd.category_id) LEFT JOIN `category_to_store` c2s ON (c2s.`category_id` = p2c.`category_id`) WHERE p2c.product_id =47543
which is basically this...
Code: Select all
SELECT * FROM " . DB_PREFIX . "category c LEFT JOIN " . DB_PREFIX . "category_description cd ON (cd.category_id = c.category_id) LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (p2c.category_id = cd.category_id) LEFT JOIN `" . DB_PREFIX . "category_to_store` c2s ON (c2s.`category_id` = p2c.`category_id`) WHERE
its shows me the names of the categories product 47543 belongs to, so its strange why its not displaying in the order edit page column
Re: Adding the category to invoices
Posted: Tue Jan 08, 2013 8:44 am
by straightlight
Enable the error logs from your OpenCart store settings. This should display the main reason why the query may fail to load from OpenCart under that store. Since today, I have added the store ID into the query for this reason, specifically.
Re: Adding the category to invoices
Posted: Tue Jan 08, 2013 4:28 pm
by harryo40
straightlight wrote:Enable the error logs from your OpenCart store settings. This should display the main reason why the query may fail to load from OpenCart under that store. Since today, I have added the store ID into the query for this reason, specifically.
Thanks again for the help & the error log is showing the 2 following...
PHP Notice: Undefined variable: category_info in order_info.tpl
PHP Warning: Invalid argument supplied for foreach() in order_info.tpl
Re: Adding the category to invoices
Posted: Wed Jan 09, 2013 8:26 am
by straightlight
Since my query is about LEFT JOIN rather than INNER JOIN, it may be possible that this category related to this store ID may still attempt to load from the selected product ID amongst the order.
To fix this, in your template, replace:
Code: Select all
<?php foreach ($category_info as $cat_info) { ?>
<?php if ((int)$order_product['product_id'] == (int)$cat_info['product_id']) { ?>
<td class="left"><?php echo $cat_info['name']; ?><br />
<?php } ?>
<?php } ?>
with:
Code: Select all
<?php if ($category_info) { ?>
<?php foreach ($category_info as $cat_info) { ?>
<?php if ((int)$order_product['product_id'] == (int)$cat_info['product_id']) { ?>
<td class="left"><?php echo $cat_info['name']; ?><br />
<?php } ?>
<?php } ?>
<?php } ?>
and ensure that the selected order product ID is relative to the category ID and it's store ID in the order.
Re: Adding the category to invoices
Posted: Fri Jan 11, 2013 12:40 am
by harryo40
Thanks for all the help again
I have tried what you said & now the error log just shoes the one warning...
PHP Notice: Undefined variable: category_info in order_info.tpl
Is it anything to do with me making the changes in the order_info.tpl instead of the order_form.tpl because if I make changes in the order_form.tpl, nothing is shown, not even the extra column?
regards
h
Re: Adding the category to invoices
Posted: Wed Jan 23, 2013 9:38 pm
by rewq111
imply by reversing psychology, when we can't get a product by categories, we have to get a category by products.
In admin/controller/sale/order.php file,
Re: Adding the category to invoices
Posted: Wed Jan 23, 2013 9:40 pm
by rewq111
straightlight wrote:Enable the error logs from your OpenCart store settings. This should display the main reason why the query may fail to load from OpenCart under that store. Since today, I have added the store ID into the query for this reason, specifically.
Re: Adding the category to invoices
Posted: Thu Jan 24, 2013 10:28 pm
by harryo40
rewq111 wrote:imply by reversing psychology, when we can't get a product by categories, we have to get a category by products.
In admin/controller/sale/order.php file,
thanks for the reply but any chance of an idea as to the coding to use?
regards
Re: Adding the category to invoices
Posted: Mon Sep 30, 2013 8:16 pm
by roylepython
Hello this was perfect for some thing im working on HOWEVER when i use it i get the following error
Notice: Error: Unknown column 'pd.name' in 'order clause'
Error No: 1054
SELECT * FROM kbb_category c LEFT JOIN kbb_category_description cd ON (cd.category_id = c.category_id) LEFT JOIN kbb_product_to_category p2c ON (p2c.category_id = cd.category_id) LEFT JOIN `kbb_category_to_store` c2s ON (c2s.`category_id` = p2c.`category_id`) WHERE cd.language_id = '1' AND p2c.product_id = '161' AND c2s.`store_id` = '0' ORDER BY pd.name ASC in /var/zpanel/hostdata/zadmin/public_html/*******/system/database/mysql.php on line 50
Should mension im using version 1.5.5.1 with mysql 5.1 and php 5.3.3
Re: Adding the category to invoices
Posted: Thu May 14, 2015 7:13 pm
by alishjee
i try this all above method but not able to even show category column in orderinfo page.
Can anyone rewrite for opencart 1.5.6.4?
Thanks in advance
Re: Adding the category to invoices
Posted: Sat Jun 27, 2015 7:13 am
by straightlight
This portion from my above post should be:
Code: Select all
public function getCategoriesByProduct($product_id, $store_id) {
$query = $this->db->query("SELECT *, `cd`.`name` AS `name` FROM " . DB_PREFIX . "category c LEFT JOIN " . DB_PREFIX . "category_description cd ON (cd.category_id = c.category_id) LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (p2c.category_id = cd.category_id) LEFT JOIN " . DB_PREFIX . "product_description pd ON (pd.product_id = p2c.product_id) LEFT JOIN " . DB_PREFIX . "product p ON (p.product_id = pd.product_id) LEFT JOIN `" . DB_PREFIX . "category_to_store` c2s ON (c2s.`category_id` = p2c.`category_id`) WHERE cd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p2c.product_id = '" . (int)$product_id . "' AND c2s.`store_id` = '" . (int)$store_id . "' ORDER BY pd.name ASC");
return $query->rows;
}
Re: Adding the category to invoices
Posted: Thu Jun 30, 2016 5:42 pm
by alishjee
which file to be edit order_info or order form?
Can you rearrange your post again? Thanks
Re: Adding the category to invoices
Posted: Thu Jun 30, 2016 6:41 pm
by straightlight
alishjee wrote:which file to be edit order_info or order form?
Can you rearrange your post again? Thanks
Neither. This modification would be for the admin/model/sale/order.php file.
Re: Adding the category to invoices
Posted: Fri Jul 01, 2016 4:20 am
by alishjee
tried every thing still not working error is
Code: Select all
Undefined variable: category_info in C:\wamp\www\testing\vqmod\vqcache\vq2-admin_view_template_sale_order_invoice.tpl on line 96