Post by straightlight » Sun Mar 17, 2019 5:47 am

In OC v3.x releases (and perhaps prior), the bestsellers extension is based on the highest prices being sold which provides the vision on noticing the ordered products that have been purchased with limited results. However, since Opencart v3.0.2.0 and above has a customer search method built-in, I thought of the idea on migrating the customer search with the ordered product results - including the updates of product options within the customer search table whenever an order has been created.

These changes now involves Demand Management with the Service Strategy as it should be. Based on the customer's service request, in this case the customer search results, store owners can now filter by total or by the most recent search queries which makes much more sense. Demands will keep increasing by customers and, in this case, for a good cause. Each products involving changes with the product options will automatically be updated in the customer search based on the last time the identical product was purchased which will only require a low amount of resource. This methodology will allow store owners to keep track of the demands vs. the sales all in the same place as for customers having an account will be able to notice the change (if not, then later on with an extension).

Edit: From now on, see from my GitHub namespace all files called: bestseller.php, catalog/model/account/search.php and catalog/model/catalog/product.php files: https://github.com/straightlight/openca ... ter/upload

Lastly, FAQ: viewtopic.php?f=176&p=739789#p718325

It would be a great feature to have among the core.
Last edited by straightlight on Fri May 10, 2019 9:09 am, edited 18 times in total.

The most generated errors being found on Opencart forum originates from contributed programming. The increased post counters are caused by redundancies of the same solutions that were already provided prior.


Regards,
Straightlight
Opencart.com contractor officials representative


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON

Post by straightlight » Tue Mar 26, 2019 10:20 am

I have now modified the getBestSellerProducts method above to a better solution. Only the maximized order product quantity would be focused with the customer search table with either completed or processing order statuses, current store, payment country and payment zone.

The most generated errors being found on Opencart forum originates from contributed programming. The increased post counters are caused by redundancies of the same solutions that were already provided prior.


Regards,
Straightlight
Opencart.com contractor officials representative


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON

Post by straightlight » Tue May 07, 2019 9:41 am

Category ID now added as a parameter from the category path.

The most generated errors being found on Opencart forum originates from contributed programming. The increased post counters are caused by redundancies of the same solutions that were already provided prior.


Regards,
Straightlight
Opencart.com contractor officials representative


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON

Post by masterross » Wed May 08, 2019 1:39 am

Hi mate,
Thanks for the idea.
I ported to v2.3.x
But I dont know how to send select var from extention to model file.
In this case type_order and limit
used in

Code: Select all

		$sql .= " ORDER BY `cs`.`date_added` " . $data['type_order'];

		$sql .= " LIMIT " . (int)$data['limit'];
Here is the whole code:

Code: Select all

<?xml version="1.0" encoding="utf-8"?>
<modification>
	<name>bestseller test</name>
	<version>1.0.1</version>
	<author>straightlight</author>
	<code>Bestsellers_per_category</code>
	<description>bestseller test modification</description>
	<link>https://forum.opencart.com</link>	

	<file path="admin/language/en-gb/extension/module/bestseller.php">
		<operation>
		<search><![CDATA[// Error]]></search>
		<add position="before"><![CDATA[$_['text_type_asc']    = 'Ascending';
$_['text_type_desc']   = 'Descending';
$_['text_type_total']  = 'Total';
$_['text_type_search'] = 'Search';
$_['text_database_transaction_delete'] = 'Delete';
$_['text_database_transaction_update'] = 'Update';

$_['entry_type']	   = 'Type';
$_['entry_type_order'] = 'Type Order';
$_['entry_database_transaction'] = 'Database Transaction';
]]></add>
		</operation>
	</file>
	<file path="admin/controller/extension/module/bestseller.php">
		<operation>
			<notes>add needed code to module</notes>
			<search><![CDATA[$data['entry_height'] = $this->language->get('entry_height');]]></search>
			<add position="after"><![CDATA[$data['text_type_asc'] = $this->language->get('text_type_asc');			
$data['text_type_desc'] = $this->language->get('text_type_desc');	
$data['text_type_total'] = $this->language->get('text_type_total');	
$data['text_type_search'] = $this->language->get('text_type_search');	
$data['text_database_transaction_delete'] = $this->language->get('text_database_transaction_delete');	
$data['text_database_transaction_update'] = $this->language->get('text_database_transaction_update');	
		
$data['entry_type'] = $this->language->get('entry_type');	
$data['entry_type_order'] = $this->language->get('entry_type_order');	
$data['entry_database_transaction'] = $this->language->get('entry_database_transaction');
]]></add>
		</operation>	    
		<operation>
			<notes>add needed code to module</notes>
			<search><![CDATA[if (isset($this->request->post['status'])) {]]></search>
			<add position="before"><![CDATA[if (isset($this->request->post['database_transaction'])) {
			$data['database_transaction'] = $this->request->post['database_transaction'];
		} elseif (!empty($module_info)) {
			$data['database_transaction'] = $module_info['database_transaction'];
		} else {
			$data['database_transaction'] = '';
		}
		
if (isset($this->request->post['type'])) {
			$data['type'] = $this->request->post['type'];
		} elseif (!empty($module_info)) {
			$data['type'] = $module_info['type'];
		} else {
			$data['type'] = '';
		}
		
		if (isset($this->request->post['type_order'])) {
			$data['type_order'] = $this->request->post['type_order'];
		} elseif (!empty($module_info)) {
			$data['type_order'] = $module_info['type_order'];
		} else {
			$data['type_order'] = '';
		}
]]></add>
		</operation>
    </file>
    

	<file path="admin/view/template/extension/module/bestseller.tpl">
		<operation>
			<notes>Add data in TPL</notes>
			<search><![CDATA[<label class="col-sm-2 control-label" for="input-status"><?php echo $entry_status; ?></label>]]></search>
			<add position="before" offset="1"><![CDATA[
		  <div class="form-group">
            <label class="col-sm-2 control-label" for="input-database-transaction"><?php echo $entry_database_transaction; ?></label>
            <div class="col-sm-10">
              <select name="database_transaction" id="input-database-transaction" class="form-control">
                <?php if ($database_transaction == 'delete') { ?>
                <option value="delete" selected="selected"><?php echo $text_database_transaction_delete; ?></option>
                <option value="update"><?php echo $text_database_transaction_update; ?></option>
                <?php } else {?>
                <option value="delete"><?php echo $text_database_transaction_delete; ?></option>
                <option value="update" selected="selected"><?php echo $text_database_transaction_update; ?></option>
                 <?php } ?>
              </select>
            </div>
          </div>
		  <div class="form-group">
            <label class="col-sm-2 control-label" for="input-type"><?php echo $entry_type; ?></label>
            <div class="col-sm-10">
              <select name="type" id="input-type" class="form-control">
                <?php if ($type == 'search') { ?>
                <option value="search" selected="selected"><?php echo $text_type_search; ?></option>
                <option value="total"><?php echo $text_type_total; ?></option>
                <?php } else {?>
                <option value="search"><?php echo $text_type_search; ?></option>
                <option value="total" selected="selected"><?php echo $text_type_total; ?></option>
                <?php } ?>
              </select>
            </div>
          </div>
		  <div class="form-group">
            <label class="col-sm-2 control-label" for="input-type_order"><?php echo $entry_type_order; ?></label>
            <div class="col-sm-10">
              <select name="type_order" id="input-type_order" class="form-control">
                <?php if ($type_order == 'desc') { ?>
                <option value="asc"><?php echo $text_type_asc; ?></option>
                <option value="desc" selected="selected"><?php echo $text_type_desc; ?></option>
                <?php } else {?>
                <option value="asc" selected="selected"><?php echo $text_type_asc; ?></option>
                <option value="desc"><?php echo $text_type_desc; ?></option>
                <?php } ?>
              </select>
            </div>
          </div>
]]></add>
		</operation>
    </file>	

	<file path="catalog/model/catalog/product.php">
		<operation>
		<search><![CDATA[public function getBestSellerProducts($limit) {]]></search>
		<add position="replace" offset="16"><![CDATA[public function getBestSellerProducts($setting, $category_id = 0) {
		$sql = "SELECT MIN(cs.date_added) AS date_start, MAX(cs.date_added) AS date_end, `cs`.`products` AS `product_id`, `cs`.`category_id`, `cs`.`sub_category`, `o`.`payment_country_id`, `o`.`payment_zone_id`, COUNT(*) AS `searches`, SUM((SELECT SUM(op1.quantity) FROM `" . DB_PREFIX . "order_product` op1 WHERE op1.product_id = cs.products GROUP BY op1.product_id)) AS products, SUM((SELECT SUM(ot.value) FROM `" . DB_PREFIX . "order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'tax' GROUP BY ot.order_id)) AS tax, SUM(o.total) AS `total` FROM `" . DB_PREFIX . "customer_search` cs INNER JOIN `" . DB_PREFIX . "order_product` `op` ON (`op`.`product_id` = `cs`.`products`) INNER JOIN `" . DB_PREFIX . "order` `o` ON (`o`.`order_id` = `op`.`order_id`)";
		
		$complete_implode = array();

		$order_statuses = $this->config->get('config_complete_status');

		foreach ($order_statuses as $order_status_id) {
			$complete_implode[] = "o.order_status_id = '" . (int)$order_status_id . "'";
		}
		
		$processing_implode = array();
		
		$order_statuses = $this->config->get('config_processing_status');

		foreach ($order_statuses as $order_status_id) {
			$processing_implode[] = "o.order_status_id = '" . (int)$order_status_id . "'";
		}

		$sql .= " WHERE (" . implode(" OR ", $complete_implode) . ")";
		$sql .= " OR (" . implode(" OR ", $processing_implode) . ")";
		
		$sql .= " AND `cs`.`customer_id` = `o`.`customer_id`";
		
		$sql .= " AND `cs`.`store_id` = '" . (int)$this->config->get('config_store_id') . "'";		
		$sql .= " AND `o`.`payment_country_id` = '" . (int)$this->config->get('config_country_id') . "'";		
		$sql .= " AND `o`.`payment_zone_id` = '" . (int)$this->config->get('config_zone_id') . "'";
		$sql .= " AND `o`.`language_id` = '" . (int)$this->config->get('config_language_id') . "'";
		
		$sql .= " AND `cs`.`store_id` = `o`.`store_id`"; 
		$sql .= " AND `cs`.`language_id` = `o`.`language_id`";
		
		if ($category_id) {
			$sql .= " AND `cs`.`category_id` = '" . (int)$category_id . "'";
		}
		
		$group = 'week';

		switch($group) {
			case 'day';
				$sql .= " GROUP BY YEAR(o.date_added), MONTH(o.date_added), DAY(o.date_added), `cs`.`products`, `cs`.`category_id`, `cs`.`sub_category`, `cs`.`store_id`, `cs`.`language_id`, `o`.`payment_country_id`, `o`.`payment_zone_id` HAVING COUNT(`op`.`quantity`) = MAX(`op`.`quantity`)";
				break;
			default:
			case 'week':
				$sql .= " GROUP BY YEAR(o.date_added), WEEK(o.date_added), `cs`.`products`, `cs`.`category_id`, `cs`.`sub_category`, `cs`.`store_id`, `cs`.`language_id`, `o`.`payment_country_id`, `o`.`payment_zone_id` HAVING COUNT(`op`.`quantity`) = MAX(`op`.`quantity`)";
				break;
			case 'month':
				$sql .= " GROUP BY YEAR(o.date_added), MONTH(o.date_added), `cs`.`products`, `cs`.`category_id`, `cs`.`sub_category`, `cs`.`store_id`, `cs`.`language_id`, `o`.`payment_country_id`, `o`.`payment_zone_id` HAVING COUNT(`op`.`quantity`) = MAX(`op`.`quantity`)";
				break;
			case 'year':
				$sql .= " GROUP BY YEAR(o.date_added), `cs`.`products`, `cs`.`category_id`, `cs`.`sub_category`, `cs`.`store_id`, `cs`.`language_id`, `o`.`payment_country_id`, `o`.`payment_zone_id` HAVING COUNT(`op`.`quantity`) = MAX(`op`.`quantity`)";
				break;
		}

		$sql .= " ORDER BY `cs`.`date_added` " . $setting['type_order'];

		$sql .= " LIMIT " . (int)$setting['limit'];

		$query = $this->db->query($sql);

		return $query->rows;
	}
]]></add>
</operation>
</file>
</modification>
Last edited by masterross on Wed May 08, 2019 6:59 am, edited 1 time in total.

New member

Posts

Joined
Mon Jan 02, 2017 7:07 pm

Post by straightlight » Wed May 08, 2019 1:47 am

But I dont know how to send select var from extention to model file.
In this case type_order and limit
Which is why, I provided the extension on my first post which includes the type_order field. It needs to be integrated from scratch simply by following my instructions above.

The most generated errors being found on Opencart forum originates from contributed programming. The increased post counters are caused by redundancies of the same solutions that were already provided prior.


Regards,
Straightlight
Opencart.com contractor officials representative


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON

Post by masterross » Wed May 08, 2019 3:06 am

Thanks for the note.
I actually did change

Code: Select all

$results = $this->model_catalog_product->getBestSellerProducts($setting['limit']);
with

Code: Select all

$category_id = 0;
		
		if (isset($this->request->get['path'])) {
			$parts = explode('_', (string)$this->request->get['path']);

			$category_id = (int)array_pop($parts);
		}

		$results = $this->model_catalog_product->getBestSellerProducts($setting['limit'], $category_id);
But I'm getting
Warning: Illegal string offset 'type_order' in /home/ceramic1/public_html/system/storage/modification/catalog/model/catalog/product.php on line 345Warning: Illegal string offset 'limit' in /home/ceramic1/public_html/system/storage/modification/catalog/model/catalog/product.php on line 347

New member

Posts

Joined
Mon Jan 02, 2017 7:07 pm

Post by straightlight » Wed May 08, 2019 3:12 am

In the admin controller part of the instructions above,

replace:

Code: Select all

if (isset($this->request->post['type_order'])) {
			$data['type_order'] = $this->request->post['type_order'];
		} elseif (!empty($module_info)) {
			$data['type_order'] = $module_info['type_order'];
		} else {
			$data['type_order'] = '';
		}
with:

Code: Select all

if (isset($this->request->post['type_order'])) {
			$data['type_order'] = $this->request->post['type_order'];
		} elseif (!empty($module_info)) {
			$data['type_order'] = $module_info['type_order'];
		} else {
			$data['type_order'] = 'ASC';
		}
Then, resave the form in the admin extension page and see if the type order error message still shows.

The most generated errors being found on Opencart forum originates from contributed programming. The increased post counters are caused by redundancies of the same solutions that were already provided prior.


Regards,
Straightlight
Opencart.com contractor officials representative


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON

Post by masterross » Wed May 08, 2019 4:42 am

Hi,
It is the same.
You probably missed that there is also "Warning: Illegal string offset 'limit' "
So the problem probably is in catalog/controller/extension/module/bestseller.php file

Of course I'm not real coder so don't blame me if I'm writing non sense :)

New member

Posts

Joined
Mon Jan 02, 2017 7:07 pm

Post by straightlight » Wed May 08, 2019 5:09 am

For now, replace in your catalog model file:

Code: Select all

$sql .= " ORDER BY `cs`.`date_added` " . $data['type_order'];
to:

Code: Select all

$sql .= " ORDER BY `cs`.`date_added`;
See if that gets rid of the error message.

The most generated errors being found on Opencart forum originates from contributed programming. The increased post counters are caused by redundancies of the same solutions that were already provided prior.


Regards,
Straightlight
Opencart.com contractor officials representative


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON

Post by masterross » Wed May 08, 2019 5:39 am

Hi,

You mean:

Code: Select all

$sql .= " ORDER BY `cs`.`date_added` " . $setting['type_order'];
To:

Code: Select all

$sql .= " ORDER BY `cs`.`date_added` ";
Now only Warning: Illegal string offset 'limit' left.

New member

Posts

Joined
Mon Jan 02, 2017 7:07 pm

Post by straightlight » Wed May 08, 2019 5:43 am

masterross wrote:
Wed May 08, 2019 5:39 am
Hi,

You mean:

Code: Select all

$sql .= " ORDER BY `cs`.`date_added` " . $setting['type_order'];
To:

Code: Select all

$sql .= " ORDER BY `cs`.`date_added` ";
Now only Warning: Illegal string offset 'limit' left.
The actual issue is that my modifications from the original instructions shows the $setting array name while on your XML file, you are using the $data array name instead which … no indications where this $data variable under the model originates from as compared to my instructions.

The most generated errors being found on Opencart forum originates from contributed programming. The increased post counters are caused by redundancies of the same solutions that were already provided prior.


Regards,
Straightlight
Opencart.com contractor officials representative


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON

Post by masterross » Wed May 08, 2019 5:49 am

I revert my data array to yours. (As I said i'm not real coder)
But I'm pointing you miss " in previous post.
Also in you original first post you wrote:

Code: Select all

{{ text_type_order_desc }}
and few other _order_ which should be:

Code: Select all

{{ text_type_desc }}
At least you declare them in this way in other files.

New member

Posts

Joined
Mon Jan 02, 2017 7:07 pm

Post by straightlight » Wed May 08, 2019 5:51 am

masterross wrote:
Wed May 08, 2019 5:49 am
I revert my data array to yours. (As I said i'm not real coder)
But I'm pointing you miss " in previous post.
Also in you original first post you wrote:

Code: Select all

{{ text_type_order_desc }}
and few other _order_ which should be:

Code: Select all

{{ text_type_desc }}
At least you declare them in this way in other files.
Confirmed. Fixed specified variables from my original code.

The most generated errors being found on Opencart forum originates from contributed programming. The increased post counters are caused by redundancies of the same solutions that were already provided prior.


Regards,
Straightlight
Opencart.com contractor officials representative


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON

Post by masterross » Wed May 08, 2019 5:12 pm

And also these:

Code: Select all

{{ text_type_order_asc }}
To:

Code: Select all

{{ text_type_asc }}
and

Code: Select all

{{ text_type_order_desc }}
to:

Code: Select all

{{ text_type_desc }}
But this doesnt help solve the problem.
I think the whole data from public function getBestSellerProducts is not sending in right way.
In original function is used cache.

New member

Posts

Joined
Mon Jan 02, 2017 7:07 pm

Post by straightlight » Wed May 08, 2019 6:21 pm

Fixed (including the IDs in the HTML form). As for the cache, this is intended since there's a bug with the DB cache versus the file cache which has been revealed on GitHub: https://github.com/opencart/opencart/issues/7392 . When available, the commit will be updated.

Thanks for reporting the minor bugs, however.

The most generated errors being found on Opencart forum originates from contributed programming. The increased post counters are caused by redundancies of the same solutions that were already provided prior.


Regards,
Straightlight
Opencart.com contractor officials representative


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON

Post by straightlight » Wed May 08, 2019 7:35 pm

- Fixed the catalog controller file to pass the entire $setting array. Forgot to replaced it back after troubleshooting the above issue yesterday.

- Added the deleteSearch support method versus order product recurring profiles to help store owners free up their database after a year without affecting their recurring periods with their customers. This will also maintain their SLAs with Service Providers as well as not keeping search results by customers for confidentially reasons after a year as per GDPR.

The most generated errors being found on Opencart forum originates from contributed programming. The increased post counters are caused by redundancies of the same solutions that were already provided prior.


Regards,
Straightlight
Opencart.com contractor officials representative


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON

Post by masterross » Thu May 09, 2019 9:19 pm

Hi mate,

Today I tested your query directly:

Code: Select all

SELECT MIN(cs.date_added) AS date_start, MAX(cs.date_added) AS date_end, `cs`.`products` AS `product_id`, `cs`.`category_id`, `cs`.`sub_category`, `o`.`payment_country_id`, `o`.`payment_zone_id`, COUNT(*) AS `searches`, SUM((SELECT SUM(op1.quantity) FROM `oc_order_product` op1 WHERE op1.product_id = cs.products GROUP BY op1.product_id)) AS products, SUM((SELECT SUM(ot.value) FROM `oc_order_total` ot WHERE ot.order_id = o.order_id AND ot.code = 'tax' GROUP BY ot.order_id)) AS tax, SUM(o.total) AS `total` FROM `oc_customer_search` cs INNER JOIN `oc_order_product` `op` ON (`op`.`product_id` = `cs`.`products`) INNER JOIN `oc_order` `o` ON (`o`.`order_id` = `op`.`order_id`)
But I cant understand why you select

Code: Select all

`cs`.`products` AS `product_id`
and then

Code: Select all

`op`.`product_id` = `cs`.`products`
Isnt it `cs`.`products` just number of products found in customer search?
How they are connected to `op`.`product_id` ?

Thanks.

New member

Posts

Joined
Mon Jan 02, 2017 7:07 pm

Post by straightlight » Thu May 09, 2019 9:27 pm

How they are connected to `op`.`product_id` ?
products from the customer_search is a misleading name as it should of been product_id as discussed on GitHub in the past already with other users. Which is why, I named an alias name for these two fields so not to get confused by what each fields provides as a role in this case.

For more information about the products field, you can always visit your catalog/model/account/search.php file.

The most generated errors being found on Opencart forum originates from contributed programming. The increased post counters are caused by redundancies of the same solutions that were already provided prior.


Regards,
Straightlight
Opencart.com contractor officials representative


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON

Post by masterross » Thu May 09, 2019 10:59 pm

Thx mate,
Can you refer where you discus about products in customer search?
I just check
catalog/model/account/search.php
where is

Code: Select all

`products` = '" . (int)$data['products'] . "'
and
catalog/controler/product/search.php
where is:

Code: Select all

				$search_data = array(
					'keyword'       => $search,
					'category_id'   => $category_id,
					'sub_category'  => $sub_category,
					'description'   => $description,
					'products'      => $product_total,
					'customer_id'   => $customer_id,
					'ip'            => $ip
				);

				$this->model_account_search->addSearch($search_data);
I also made a simple test with Search as customer and i can confirm that products field contains the number of found products in search I made.
What am I doing wrong?

P.S.
I test in v2.3
But sense i compared the search file they are identical.
What am i doin wrong?

New member

Posts

Joined
Mon Jan 02, 2017 7:07 pm

Post by straightlight » Fri May 10, 2019 12:35 am

I seem to have overlooked something here. I will post an update soon.

The most generated errors being found on Opencart forum originates from contributed programming. The increased post counters are caused by redundancies of the same solutions that were already provided prior.


Regards,
Straightlight
Opencart.com contractor officials representative


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON
Who is online

Users browsing this forum: No registered users and 2 guests