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.
Dedication and passion goes to those who are able to push and merge a project.
Regards,
Straightlight
Programmer / Opencart Tester
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.
Dedication and passion goes to those who are able to push and merge a project.
Regards,
Straightlight
Programmer / Opencart Tester
Category ID now added as a parameter from the category path.
Dedication and passion goes to those who are able to push and merge a project.
Regards,
Straightlight
Programmer / Opencart Tester
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
Here is the whole code:
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'];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.
Pottery Glaze shop
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.But I dont know how to send select var from extention to model file.
In this case type_order and limit
Dedication and passion goes to those who are able to push and merge a project.
Regards,
Straightlight
Programmer / Opencart Tester
Thanks for the note.
I actually did change
with
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
I actually did change
Code: Select all
$results = $this->model_catalog_product->getBestSellerProducts($setting['limit']);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);
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
Pottery Glaze shop
In the admin controller part of the instructions above,
replace:
with:
Then, resave the form in the admin extension page and see if the type order error message still shows.
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'] = '';
}
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';
}
Dedication and passion goes to those who are able to push and merge a project.
Regards,
Straightlight
Programmer / Opencart Tester
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
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
Pottery Glaze shop
For now, replace in your catalog model file:
to:
See if that gets rid of the error message.
Code: Select all
$sql .= " ORDER BY `cs`.`date_added` " . $data['type_order'];
Code: Select all
$sql .= " ORDER BY `cs`.`date_added`;
Dedication and passion goes to those who are able to push and merge a project.
Regards,
Straightlight
Programmer / Opencart Tester
Hi,
You mean:
To:
Now only Warning: Illegal string offset 'limit' left.
You mean:
Code: Select all
$sql .= " ORDER BY `cs`.`date_added` " . $setting['type_order'];Code: Select all
$sql .= " ORDER BY `cs`.`date_added` ";Pottery Glaze shop
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.masterross wrote: ↑Wed May 08, 2019 5:39 amHi,
You mean:To:Code: Select all
$sql .= " ORDER BY `cs`.`date_added` " . $setting['type_order'];Now only Warning: Illegal string offset 'limit' left.Code: Select all
$sql .= " ORDER BY `cs`.`date_added` ";
Dedication and passion goes to those who are able to push and merge a project.
Regards,
Straightlight
Programmer / Opencart Tester
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:
and few other _order_ which should be:
At least you declare them in this way in other files.
But I'm pointing you miss " in previous post.
Also in you original first post you wrote:
Code: Select all
{{ text_type_order_desc }}Code: Select all
{{ text_type_desc }}Pottery Glaze shop
Confirmed. Fixed specified variables from my original code.masterross wrote: ↑Wed May 08, 2019 5:49 amI 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:and few other _order_ which should be:Code: Select all
{{ text_type_order_desc }}
At least you declare them in this way in other files.Code: Select all
{{ text_type_desc }}
Dedication and passion goes to those who are able to push and merge a project.
Regards,
Straightlight
Programmer / Opencart Tester
And also these:
To:
and
to:
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.
Code: Select all
{{ text_type_order_asc }}Code: Select all
{{ text_type_asc }}Code: Select all
{{ text_type_order_desc }}Code: Select all
{{ text_type_desc }}I think the whole data from public function getBestSellerProducts is not sending in right way.
In original function is used cache.
Pottery Glaze shop
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.
Thanks for reporting the minor bugs, however.
Dedication and passion goes to those who are able to push and merge a project.
Regards,
Straightlight
Programmer / Opencart Tester
- 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.
- 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.
Dedication and passion goes to those who are able to push and merge a project.
Regards,
Straightlight
Programmer / Opencart Tester
Hi mate,
Today I tested your query directly:
But I cant understand why you select and then
Isnt it `cs`.`products` just number of products found in customer search?
How they are connected to `op`.`product_id` ?
Thanks.
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`)Code: Select all
`cs`.`products` AS `product_id`Code: Select all
`op`.`product_id` = `cs`.`products`How they are connected to `op`.`product_id` ?
Thanks.
Pottery Glaze shop
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.How they are connected to `op`.`product_id` ?
For more information about the products field, you can always visit your catalog/model/account/search.php file.
Dedication and passion goes to those who are able to push and merge a project.
Regards,
Straightlight
Programmer / Opencart Tester
Thx mate,
Can you refer where you discus about products in customer search?
I just check
catalog/model/account/search.php
where is
and
catalog/controler/product/search.php
where is:
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?
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'] . "'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);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?
Pottery Glaze shop
I seem to have overlooked something here. I will post an update soon.
Dedication and passion goes to those who are able to push and merge a project.
Regards,
Straightlight
Programmer / Opencart Tester
Who is online
Users browsing this forum: No registered users and 72 guests
