Adding the category to invoices

Quote

Post by mkc » Tue Nov 15, 2011 6:17 am

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.
mkc
New member
Posts
27
Joined
Sun Nov 06, 2011 11:20 pm
Top

Re: Adding the category to invoices

Quote

Post by mkc » Mon Nov 21, 2011 11:56 am

Anyone? Anything? Very happening place...
mkc
New member
Posts
27
Joined
Sun Nov 06, 2011 11:20 pm
Top

Re: Adding the category to invoices

Quote

Post by straightlight » Thu Nov 24, 2011 9:50 am

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:

Code: Select all

$this->data['order_products'][]
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:

Code: Select all

$_['column_model']            = 'Model';
add above:

Code: Select all

$_['column_category']         = 'Categories';
All set.

Dedication and passion goes to those who are able to push and merge a project.

Regards,
Straightlight
Programmer / Opencart Tester

straightlight
Legendary Member
Posts
22525
Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON
Top

Re: Adding the category to invoices

Quote

Post by harryo40 » Mon Jan 07, 2013 9:20 pm

@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 ;)

Add To Cart Confirm Ajax Popup for OC 1.5.1.3 --> 1.5.5.1 - Add to Cart Confirmation Popup
Image Map Banner Module - Image Map Banner Module
----------------------------------------------------------------------
Womens Famous Name Fashion Clothing at bargain prices - Next2nowt.com

harryo40
Active Member
Posts
205
Joined
Wed Oct 21, 2009 3:37 am
Location - Blackburn, Lancashire
Top

Re: Adding the category to invoices

Quote

Post by straightlight » Mon Jan 07, 2013 9:51 pm

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.

Dedication and passion goes to those who are able to push and merge a project.

Regards,
Straightlight
Programmer / Opencart Tester

straightlight
Legendary Member
Posts
22525
Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON
Top

Re: Adding the category to invoices

Quote

Post by harryo40 » Mon Jan 07, 2013 10:34 pm

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

Add To Cart Confirm Ajax Popup for OC 1.5.1.3 --> 1.5.5.1 - Add to Cart Confirmation Popup
Image Map Banner Module - Image Map Banner Module
----------------------------------------------------------------------
Womens Famous Name Fashion Clothing at bargain prices - Next2nowt.com

harryo40
Active Member
Posts
205
Joined
Wed Oct 21, 2009 3:37 am
Location - Blackburn, Lancashire
Top

Re: Adding the category to invoices

Quote

Post by harryo40 » Mon Jan 07, 2013 11:03 pm

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 ???

Add To Cart Confirm Ajax Popup for OC 1.5.1.3 --> 1.5.5.1 - Add to Cart Confirmation Popup
Image Map Banner Module - Image Map Banner Module
----------------------------------------------------------------------
Womens Famous Name Fashion Clothing at bargain prices - Next2nowt.com

harryo40
Active Member
Posts
205
Joined
Wed Oct 21, 2009 3:37 am
Location - Blackburn, Lancashire
Top

Re: Adding the category to invoices

Quote

Post by straightlight » Tue Jan 08, 2013 8:44 am

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. ;)

Dedication and passion goes to those who are able to push and merge a project.

Regards,
Straightlight
Programmer / Opencart Tester

straightlight
Legendary Member
Posts
22525
Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON
Top

Re: Adding the category to invoices

Quote

Post by harryo40 » Tue Jan 08, 2013 4:28 pm

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

Add To Cart Confirm Ajax Popup for OC 1.5.1.3 --> 1.5.5.1 - Add to Cart Confirmation Popup
Image Map Banner Module - Image Map Banner Module
----------------------------------------------------------------------
Womens Famous Name Fashion Clothing at bargain prices - Next2nowt.com

harryo40
Active Member
Posts
205
Joined
Wed Oct 21, 2009 3:37 am
Location - Blackburn, Lancashire
Top

Re: Adding the category to invoices

Quote

Post by straightlight » Wed Jan 09, 2013 8:26 am

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.

Dedication and passion goes to those who are able to push and merge a project.

Regards,
Straightlight
Programmer / Opencart Tester

straightlight
Legendary Member
Posts
22525
Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON
Top

Re: Adding the category to invoices

Quote

Post by harryo40 » Fri Jan 11, 2013 12:40 am

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

Add To Cart Confirm Ajax Popup for OC 1.5.1.3 --> 1.5.5.1 - Add to Cart Confirmation Popup
Image Map Banner Module - Image Map Banner Module
----------------------------------------------------------------------
Womens Famous Name Fashion Clothing at bargain prices - Next2nowt.com

harryo40
Active Member
Posts
205
Joined
Wed Oct 21, 2009 3:37 am
Location - Blackburn, Lancashire
Top

Re: Adding the category to invoices

Quote

Post by rewq111 » Wed Jan 23, 2013 9:38 pm

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,
rewq111
Newbie
Posts
4
Joined
Tue Jan 22, 2013 5:51 pm
Top

Re: Adding the category to invoices

Quote

Post by rewq111 » Wed Jan 23, 2013 9:40 pm

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. ;)
rewq111
Newbie
Posts
4
Joined
Tue Jan 22, 2013 5:51 pm
Top

Re: Adding the category to invoices

Quote

Post by harryo40 » Thu Jan 24, 2013 10:28 pm

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

Add To Cart Confirm Ajax Popup for OC 1.5.1.3 --> 1.5.5.1 - Add to Cart Confirmation Popup
Image Map Banner Module - Image Map Banner Module
----------------------------------------------------------------------
Womens Famous Name Fashion Clothing at bargain prices - Next2nowt.com

harryo40
Active Member
Posts
205
Joined
Wed Oct 21, 2009 3:37 am
Location - Blackburn, Lancashire
Top

Re: Adding the category to invoices

Quote

Post by roylepython » Mon Sep 30, 2013 8:16 pm

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
roylepython
New member
Posts
23
Joined
Wed Sep 18, 2013 5:18 am
Top

Re: Adding the category to invoices

Quote

Post by alishjee » Thu May 14, 2015 7:13 pm

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
alishjee
Newbie
Posts
3
Joined
Thu May 14, 2015 7:11 pm
Top

Re: Adding the category to invoices

Quote

Post by straightlight » Sat Jun 27, 2015 7:13 am

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;
       }

Dedication and passion goes to those who are able to push and merge a project.

Regards,
Straightlight
Programmer / Opencart Tester

straightlight
Legendary Member
Posts
22525
Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON
Top

Re: Adding the category to invoices

Quote

Post by alishjee » Thu Jun 30, 2016 5:42 pm

which file to be edit order_info or order form?

Can you rearrange your post again? Thanks
alishjee
Newbie
Posts
3
Joined
Thu May 14, 2015 7:11 pm
Top

Re: Adding the category to invoices

Quote

Post by straightlight » Thu Jun 30, 2016 6:41 pm

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.

Dedication and passion goes to those who are able to push and merge a project.

Regards,
Straightlight
Programmer / Opencart Tester

straightlight
Legendary Member
Posts
22525
Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON
Top

Re: Adding the category to invoices

Quote

Post by alishjee » Fri Jul 01, 2016 4:20 am

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
alishjee
Newbie
Posts
3
Joined
Thu May 14, 2015 7:11 pm
Top
Post Reply
Return to “Concepts”
Who is online

Users browsing this forum: No registered users and 6 guests