Post by Buzzfpv » Fri Jan 14, 2022 6:22 pm

Hi Smart people - is there a way to audit the stock levels in the database ? a log file i can search ?
i feel that someone might have access and is messing with my stock levels
every few months i feel that items that have not been in stock for a long time suddenly seem to be in stock and I'm having to apologise to my customers and refund.
is there a way i can check when and how the stock level was increased ?
any sql log files ? audit trails

I have changed passwords and have tried to protect the site - but every now and then the stock levels are out of whack that i can't work out how

Newbie

Posts

Joined
Fri Jun 14, 2019 12:47 pm

Post by khnaz35 » Fri Jan 14, 2022 6:30 pm

Buzzfpv wrote:
Fri Jan 14, 2022 6:22 pm
Hi Smart people - is there a way to audit the stock levels in the database ? a log file i can search ?
i feel that someone might have access and is messing with my stock levels
every few months i feel that items that have not been in stock for a long time suddenly seem to be in stock and I'm having to apologise to my customers and refund.
is there a way i can check when and how the stock level was increased ?
any sql log files ? audit trails

I have changed passwords and have tried to protect the site - but every now and then the stock levels are out of whack that i can't work out how
Firstly do let us know which OC version and theme you are using?
Second double check your Catalog >product page edit and look under Data Tab what is the status select for Subtract Stock

Urgent Questions shoot here: khnaz35@gmail.com
Enjoy nature ;) :) :-*


User avatar
Active Member

Posts

Joined
Mon Aug 27, 2018 11:30 pm
Location - Malaysia

Post by Buzzfpv » Fri Jan 14, 2022 6:40 pm

Thanks for the reply
I am using Opencart 3.032 and i am using journal theme - i don't believe it's a journal issue - i could be wrong - but just trying to track the issue down
the system is reducing stock levels after sales as expected.

do any log files exist that audit's stock levels - hoping to search for a specific product and see when it has increased above zero value

Newbie

Posts

Joined
Fri Jun 14, 2019 12:47 pm

Post by khnaz35 » Fri Jan 14, 2022 7:16 pm

Buzzfpv wrote:
Fri Jan 14, 2022 6:40 pm
Thanks for the reply
I am using Opencart 3.032 and i am using journal theme - i don't believe it's a journal issue - i could be wrong - but just trying to track the issue down
the system is reducing stock levels after sales as expected.

do any log files exist that audit's stock levels - hoping to search for a specific product and see when it has increased above zero value
You didn't answer on the second step.

Urgent Questions shoot here: khnaz35@gmail.com
Enjoy nature ;) :) :-*


User avatar
Active Member

Posts

Joined
Mon Aug 27, 2018 11:30 pm
Location - Malaysia

Post by paulfeakins » Fri Jan 14, 2022 7:23 pm

Buzzfpv wrote:
Fri Jan 14, 2022 6:22 pm
is there a way i can check when and how the stock level was increased ?
any sql log files ? audit trails
It depends what's set up on your server, so you should ask your web host or pay an experienced developer to have a look and make sure logging is set up in future.

Paul.

UK OpenCart Hosting | OpenCart Audits | OpenCart Support - please email info@antropy.co.uk


User avatar
Guru Member
Online

Posts

Joined
Mon Aug 22, 2011 11:01 pm
Location - London Gatwick, United Kingdom

Post by by mona » Fri Jan 14, 2022 8:46 pm

Normally, i.e. not journal, stock management is done when adding order history.
You could, actually should, log what is going on there, maybe even create a separate checkout log file for it (not just stock).

in catalog/model/checkout/order.php function addOrderHistory

you could add:

Code: Select all

			if (AUDIT_TRAIL_ORDER_STOCK) {
				$csql = "select name from ".DB_PREFIX."order_status where order_status_id = ".$order_info['order_status_id']." and language_id = ".$this->config->get('config_language_id');
				$results = $this->db->query($csql);
				$fromstatus = (!empty($results->row['name']) ? $results->row['name'] : $order_info['order_status_id']);
				$csql = "select name from ".DB_PREFIX."order_status where order_status_id = ".$order_status_id." and language_id = ".$this->config->get('config_language_id');
				$results = $this->db->query($csql);
				$tostatus = (!empty($results->row['name']) ? $results->row['name'] : $order_status_id);	
				$this->log->write('****************** Stock audit trail (assuming subtract stock enabled) *********************************');	
				$this->log->write($_SERVER['REMOTE_ADDR'].' Order History: Order '.$order_id.' to be moved from status '.$fromstatus.' to '.$tostatus);	
			}

before:

Code: Select all

// If current order status is not processing or complete but new status is processing or complete then commence completing the order
add:

Code: Select all

					if (AUDIT_TRAIL_ORDER_STOCK) {
						$this->log->write($_SERVER['REMOTE_ADDR'].' Order History: Order '.$order_id.' subtracting '.(int)$order_product['quantity'].' stock for product '.(int)$order_product['product_id']);
					}

after:

Code: Select all

				// Stock subtraction
				$order_products = $this->getOrderProducts($order_id);

				foreach ($order_products as $order_product) {

add:

Code: Select all

						if (AUDIT_TRAIL_ORDER_STOCK) {
							$this->log->write($_SERVER['REMOTE_ADDR'].' Order History: Order '.$order_id.' subtracting '.(int)$order_product['quantity'].' stock for product option '.(int)$order_option['product_option_value_id']);
						}

after:

Code: Select all

					$order_options = $this->getOrderOptions($order_id, $order_product['order_product_id']);

					foreach ($order_options as $order_option) {

add:

Code: Select all

					if (AUDIT_TRAIL_ORDER_STOCK) {
						$this->log->write($_SERVER['REMOTE_ADDR'].' Order History: Order '.$order_id.' restocking '.(int)$order_product['quantity'].' stock for product '.(int)$order_product['product_id']);
					}

after:

Code: Select all

				// Restock
				$order_products = $this->getOrderProducts($order_id);

				foreach($order_products as $order_product) {

add:

Code: Select all

						if (AUDIT_TRAIL_ORDER_STOCK) {
							$this->log->write($_SERVER['REMOTE_ADDR'].' Order History: Order '.$order_id.' restocking '.(int)$order_product['quantity'].' stock for product option '.(int)$order_option['product_option_value_id']);
						}

after:

Code: Select all

					$order_options = $this->getOrderOptions($order_id, $order_product['order_product_id']);

					foreach ($order_options as $order_option) {
						

add:

Code: Select all

define('AUDIT_TRAIL_ORDER_STOCK', true); // switch logging on/off
to both config.php files

this will give you something like:

Code: Select all

2022-01-14 19:39:32 - ****************** Stock audit trail (assuming subtract stock enabled) *********************************
2022-01-14 19:39:32 - xxx.xxx.xxx.xxx Order History: Order 2138 to be moved from status 0 to Pending
2022-01-14 19:39:32 - xxx.xxx.xxx.xxx Order History: Order 2138 subtracting 1 stock for product 1318
2022-01-14 20:00:23 - ****************** Stock audit trail (assuming subtract stock enabled) *********************************
2022-01-14 20:00:23 - xxx.xxx.xxx.xxx Order History: Order 2138 to be moved from status Pending to Canceled
2022-01-14 20:00:23 - xxx.xxx.xxx.xxx Order History: Order 2138 restocking 1 stock for product 1318

DISCLAIMER:
You should not modify core files .. if you would like to donate a cup of coffee I will write it in a modification for you.


https://www.youtube.com/watch?v=zXIxDoCRc84


User avatar
Expert Member

Posts

Joined
Mon Jun 10, 2019 9:31 am
Who is online

Users browsing this forum: Semrush [Bot] and 258 guests