Adding the category to invoices

[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.

[mkc]

Anyone? Anything? Very happening place...

[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:

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.

[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

[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.

[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

[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

[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.

[harryo40]

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

[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.

[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

[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,


<a title="مظلات وسواتر"href=http://shadess.ucoz.com/>مظلات وسواتر</a>

<a title="سواتر ومظلات"href=http://www.mdemig.com/>سواتر ومظلات</a>

<a title="مظلات"href=http://sawater.ucoz.net/>سواتر ومظلات</a>

[rewq111]

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.

<a title="مظلات وسواتر"href=http://shadess.ucoz.com/>مظلات وسواتر</a>

<a title="سواتر ومظلات"href=http://www.mdemig.com/>سواتر ومظلات</a>

<a title="مظلات"href=http://sawater.ucoz.net/>سواتر ومظلات</a>

[harryo40]

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