Attached are evidences of the issues in pictorial form. We are tired of the issue and need URGENT HELP

Thank you

Hello,

I am sorry but I didn't get the issue. Are you using the item quantity is being changed without any sales/purchase/adjustment or the quantity on list products not don't match with details?

I will need details and if you could provide steps to reproduce the issue.

Thank you

Yes exactly.

The item quantity changes on its own without any sales/purchase/adjustment. As a result the quantity on list products not don't match with the physical quantity in the store.

For details, kindly look at the images I sent above. They are all one product but at different times of the day the quantity changes with no human intervention nor sales. All those changes where witnessed in the night.

Please how do I send you the login for you to observe in realtime.

Thank you

Hello,

That is quite strange. Do you have custom changes to the item?

You can added private reply by enabling the `PRIVATE for Staff only` at top right of the reply section.

Thank you

**[Mian Saleem](/u/saleem)** Hi Mian Saleem,

I believe this is one of the clients that did contact me over the week for Stock Quantity issues, and on reviewing their stock and the SMA scripts.

I discovered the major bug, among other minor bugs to be in the `resetSaleActions()` function in the `Sales_model`.

Time will not permit me to provide full details of my findings now but the summary is that each time, a sale that the `purchase_id` in `sma_costing` table is `NULL`, is modified/edited, it reduces the quantity balance of the various products by the quantities sold. I could trace the issue back to `version 3.4.36` when the `purchase_id` column was added to the `sma_costing` table.


**Below are the steps I used in recreating the issue on SMA Demo System**.

1. Added one more warehouse to make the warehouses on the Demo three (Warehouse 1, 2 & 3)

2. Added a Non-variant Product (`Product A`)

3. Purchased `1000pcs` of `Product A` to `Warehouse 1`.

4. Transferred `500pcs` of `Product A` to `Warehouse 2` and `300pcs` to `Warehouse 3` (both from `Warehouse 1`)

5. Added a `Completed Sale` of `200pcs` of `Product A` from `Warehouse 2`, via `Add Sale` page

The `quantity balance` of `Product A` in `warehouse 2` = `100pc`. But on **editing the posted sale without changing the quantity**, the `quantity balance` of `Product A` in` warehouse 2` **changes to** `-100pcs` **(minus)**.

**Note:** Warehouse 2 & 3 got their **Stock-In via product transfer**, hence `purchase_id` was `NULL` on `sma_purchase_items` which lead to `purchase_id` in `sma_costing` for the sale to be `NULL` as well.

Then getting `$purchase_items` via *$purchase_items* = `$this->getPurchaseItems($costing->purchase_id)` in `resetSaleActions()` function, will pull as many records that are available from different warehouses where `purchase_id` is `NULL`, hence resulting to inaccurate **Product Qty Sync**.

**FIX**

1. Initilizing `$pi` variable to `NULL`

2. [CASE A]. Test if `$costing->purchase_id` is **not** `[NULL, empty or less than zero]` before getting *$purchase_items* via `$purchase_items = $this->getPurchaseItems($costing->purchase_id)`.

3. [CASE B]. Added another function `getPurchaseItemByPurchaseItemId($costing->purchase_item_id)` to get *$purchase_items* using `purchase_item_id` in the `sma_costing` table if CASE A fails.

4. [CASE C]. Added another function `$this->getPurchaseItemsByPurchaseIdAndWarehouseId($costing->purchase_id, $sale->warehouse_id)` to get *$purchase_items* using `purchase_id` (NULL) and `warehouse_id` in the `sma_costing` table if CASE B fails.


**See the modified codes below**.

**resetSaleAction function**


public function resetSaleActions($id, $return_id = null, $check_return = null)
{
if ($sale = $this->getInvoiceByID($id)) {
if ($check_return && $sale->sale_status == 'returned') {
$this->session->set_flashdata('warning', lang('sale_x_action'));
redirect($_SERVER['HTTP_REFERER'] ?? 'welcome');
}
if ($sale->sale_status == 'completed') {
if ($costings = $this->getSaleCosting($id)) {
foreach ($costings as $costing) {
// initilialize $pi to null
$pi = null;

// Test if $costing->purchase_id is NULL or empty or less than zero [CASE A]
if ($costing->purchase_id && $costing->purchase_id != null && !empty($costing->purchase_id) && $costing->purchase_id > 0) {
$purchase_items = $this->getPurchaseItems($costing->purchase_id);
foreach ($purchase_items as $row) {
if ($row->product_id == $costing->product_id && $row->option_id == $costing->option_id) {
$pi = $row;
log_message('error', 'More than zero: ' . $quantity_balance . ' = ' . $pi->quantity_balance . ' + ' . $qty . ' PI: ' . print_r($pi, true));
}
}
} else {
// If [CASE A] fails try getting $purchase_items using purchase_item_id in costing table [CASE B]
$purchase_items = $this->getPurchaseItemByPurchaseItemId($costing->purchase_item_id);
if ($purchase_items) {
foreach ($purchase_items as $row) {
$pi = $row;
}
} else {
// If [CASE B] fails try getting $purchase_items using purchase_id and warehouse in costing table
$purchase_items = $this->getPurchaseItemsByPurchaseIdAndWarehouseId($costing->purchase_id, $sale->warehouse_id);
foreach ($purchase_items as $row) {
if ($row->product_id == $costing->product_id && $row->option_id == $costing->option_id) {
$pi = $row;
}
}
}
}
if ($pi) {
$this->site->setPurchaseItem(['id' => $pi->id, 'product_id' => $pi->product_id, 'option_id' => $pi->option_id], $costing->quantity);
} else {
$pi = $this->site->getPurchasedItem(['product_id' => $costing->product_id, 'option_id' => $costing->option_id ? $costing->option_id : null, 'purchase_id' => null, 'transfer_id' => null, 'warehouse_id' => $sale->warehouse_id]);
$this->site->setPurchaseItem(['id' => $pi->id, 'product_id' => $pi->product_id, 'option_id' => $pi->option_id], $costing->quantity);
}
}
$this->db->delete('costing', ['id' => $costing->id]);
}
$items = $this->getAllInvoiceItems($id);
$this->site->syncQuantity(null, null, $items);
$this->sma->update_award_points($sale->grand_total, $sale->customer_id, $sale->created_by, true);
return $items;
}
}
}

**Two other functions**

public function getPurchaseItemByPurchaseItemId($purchase_item_id)
{
return $this->db->get_where('purchase_items', ['id' => $purchase_item_id])->result();
}

public function getPurchaseItemsByPurchaseIdAndWarehouseId($purchase_id, $warehouse_id)
{
return $this->db->get_where('purchase_items', ['purchase_id' => $purchase_id, 'warehouse_id' => $warehouse_id])->result();
}

**[Enyinnaya Gift](/u/giftedhands4real)** Excellent finding! I will check and add the suggestions in next update. Thank you very much for sharing

**[Mian Saleem](/u/saleem)** You are welcome

**[Mian Saleem](/u/saleem)** Another fix to add in the next update is on `updateStatus()` function in `Sales_model`. When a completed sale's status is updated via **Update Status Modal** it deletes the sale items from the `sma_costing` table and this, in turn, affects the **Product Report** since the **Product Sold Qty** is calculated from the `sma_costing` table.

**Below are the steps I used in recreating the issue on SMA Demo System.**
1. Pick a Product [Product A] and post a completed sale order on it [Product A].
2. Check the Product Report of the Product [Product A] via Reports->Product Report, and note down the Sold Qty.
3. Return to the List Sales page and update the status of the Product A sale without changing the status from completed.
4. Recheck the Product Report of [Product A], you will see that the Sold Qty has reduced.

**FIX**
Replace the following lines in `updateStatus()` function in `Sales_model`


if ($this->db->update('sales', ['sale_status' => $status, 'note' => $note], ['id' => $id]) && $this->db->delete('costing', ['sale_id' => $id])) {
if ($status == 'completed' && $sale->sale_status != 'completed') {

With

if ($this->db->update('sales', ['sale_status' => $status, 'note' => $note], ['id' => $id])) {
if ($status == 'completed' && $sale->sale_status != 'completed') {
// Fixed bug of deleting from sma_costing
$this->db->delete('costing', ['sale_id' => $id]);

**NB**:- For already affected Sales, updating/resubmitting the Sales by **Sale Edit** fixes the issue.

Hello Support,

Please where are we on this issue?

I have read the responses of Enyinnaya Gift. Based on all that he has done, is the issue completely resolved now?

Kindly advice.

Thank you

**[Enyinnaya Gift](/u/giftedhands4real)** Hello,

That `updateStatus` method is already changed to the following

```php
public function updateStatus($id, $status, $note)
{
$this->db->trans_start();
$sale = $this->getInvoiceByID($id);
$items = $this->getAllInvoiceItems($id);
$cost = [];

foreach ($items as $item) {
$items_array[] = (array) $item;
}
$cost = $this->site->costing($items_array);
$this->resetSaleActions($id);

if ($this->db->update('sales', ['sale_status' => $status, 'note' => $note], ['id' => $id]) && $this->db->delete('costing', ['sale_id' => $id])) {
foreach ($items as $item) {
$item = (array) $item;
if ($this->site->getProductByID($item['product_id'])) {
$item_costs = $this->site->item_costing($item);
foreach ($item_costs as $item_cost) {
if (isset($item_cost['date']) || isset($item_cost['pi_overselling'])) {
$item_cost['sale_item_id'] = $item['id'];
$item_cost['sale_id'] = $id;
$item_cost['date'] = date('Y-m-d', strtotime($sale->date));
if (!isset($item_cost['pi_overselling'])) {
$this->db->insert('costing', $item_cost);
}
} else {
foreach ($item_cost as $ic) {
$ic['sale_item_id'] = $item['id'];
$ic['sale_id'] = $id;
$ic['date'] = date('Y-m-d', strtotime($sale->date));
if (!isset($ic['pi_overselling'])) {
$this->db->insert('costing', $ic);
}
}
}
}
}
}

if (!empty($cost)) {
$this->site->syncPurchaseItems($cost);
}
$this->site->syncQuantity($id);
}
$this->db->trans_complete();
if ($this->db->trans_status() === false) {
log_message('error', 'An errors has been occurred while adding the sale (UpdataStatus:Sales_model.php)');
} else {
return true;
}
return false;
}
```

Please test with this and let me know your thoughts.

Thank you