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 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 inwarehouse 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 Excellent finding! I will check and add the suggestions in next update. Thank you very much for sharing

Mian Saleem You are welcome

Mian 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 Hello,

That updateStatus method is already changed to the following

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