Replenishment logic in warehouse management

  algorithm, laravel, php

I’m trying to create a replenish list for warehouse workers, who will walk around a warehouse picking products in orders.

First, I wanna show
Order Data and storage data

Orders Data

Storage Data

Logic

  1. Storage Must order By Exp_date, location type, and location_id
  2. If storage qty bigger than left outstanding make replenishment
  3. If has remain after replenish, use that location for next order id and fill all remain to next order
  4. Replenishment just for type Rack, For example: if i request order 7 and first location type is pick and have qty 8, no need replenisment

this is
Result i hope

DROP TABLE IF EXISTS `storages`;
CREATE TABLE `storages`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `location_id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `item_id` int(11) NULL DEFAULT NULL,
  `batch` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `exp_date` date NULL DEFAULT NULL,
  `qty` int(11) NULL DEFAULT NULL,
  `pick_qty` int(11) NULL DEFAULT NULL,
  `put_qty` int(11) NULL DEFAULT NULL,
  `stock_type_id` int(11) NULL DEFAULT NULL,
  `location_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 72 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of storages
-- ----------------------------
INSERT INTO `storages` VALUES (21, 'M-16-10', 1, '092021', '2024-08-16', 8, 0, 0 , 1, 'PICK');
INSERT INTO `storages` VALUES (22, 'K-14-40', 1, '092021', '2024-08-16', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (23, 'K-15-60', 1, '092021', '2024-08-16', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (24, 'K-17-50', 1, '092021', '2024-08-16', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (25, 'K-20-60', 1, '092021', '2024-08-16', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (26, 'K-24-60', 1, '092021', '2024-08-16', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (27, 'K-26-40', 1, '092021', '2024-08-16', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (28, 'K-27-20', 1, '092021', '2024-08-16', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (29, 'K-32-40', 1, '092021', '2024-08-16', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (30, 'K-33-50', 1, '092021', '2024-08-16', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (31, 'K-34-20', 1, '092021', '2024-08-16', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (32, 'K-36-60', 1, '092021', '2024-08-16', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (33, 'K-38-60', 1, '092021', '2024-08-16', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (34, 'K-47-20', 1, '092021', '2024-08-16', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (35, 'K-50-20', 1, '092021', '2024-08-16', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (36, 'K-51-60', 1, '092021', '2024-08-16', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (37, 'K-53-20', 1, '092021', '2024-08-16', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (38, 'K-53-60', 1, '092021', '2024-08-16', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (39, 'K-54-30', 1, '092021', '2024-08-16', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (40, 'K-63-40', 1, '092021', '2024-08-16', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (41, 'K-65-20', 1, '092021', '2024-08-16', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (42, 'M-09-40', 1, '092021', '2024-08-17', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (43, 'M-13-60', 1, '092021', '2024-08-17', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (44, 'M-14-40', 1, '092021', '2024-08-17', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (45, 'M-16-30', 1, '092021', '2024-08-17', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (46, 'N-49-50', 1, '092021', '2024-08-17', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (47, 'N-49-60', 1, '092021', '2024-08-17', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (48, 'N-50-50', 1, '092021', '2024-08-17', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (49, 'N-50-60', 1, '092021', '2024-08-17', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (50, 'N-51-20', 1, '092021', '2024-08-17', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (51, 'N-51-30', 1, '092021', '2024-08-17', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (52, 'N-51-40', 1, '092021', '2024-08-17', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (53, 'N-51-50', 1, '092021', '2024-08-17', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (54, 'N-51-60', 1, '092021', '2024-08-18', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (55, 'N-52-20', 1, '092021', '2024-08-18', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (56, 'N-52-40', 1, '092021', '2024-08-18', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (57, 'N-52-50', 1, '092021', '2024-08-18', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (58, 'N-52-60', 1, '092021', '2024-08-18', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (59, 'N-53-20', 1, '092021', '2024-08-18', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (60, 'N-53-30', 1, '092021', '2024-08-18', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (61, 'N-53-40', 1, '092021', '2024-08-18', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (62, 'N-53-50', 1, '092021', '2024-08-18', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (63, 'N-53-60', 1, '092021', '2024-08-18', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (64, 'N-54-20', 1, '092021', '2024-08-18', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (65, 'N-54-30', 1, '092021', '2024-08-18', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (66, 'N-54-40', 1, '092021', '2024-08-18', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (67, 'N-54-50', 1, '092021', '2024-08-18', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (68, 'N-54-60', 1, '092021', '2024-08-18', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (69, 'N-55-20', 1, '092021', '2024-08-18', 52, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (70, 'N-55-30', 1, '092021', '2024-08-18', 9, 0, 0 , 1, 'RACK');
INSERT INTO `storages` VALUES (71, 'N-56-20', 1, '092021', '2024-08-18', 52, 0, 0 , 1, 'RACK');

And table order looks like

DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item_id` mediumint(11) NOT NULL,
  `batch` varchar(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `exp_date` date NULL DEFAULT NULL,
  `qty` double(20, 3) NOT NULL,
  `pick_qty` double(20, 3) NULL DEFAULT 0,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5411 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of orders
-- ----------------------------
INSERT INTO `orders` VALUES (5059, 7485, NULL, NULL, 25, 0.000);
INSERT INTO `orders` VALUES (5077, 7485, NULL, NULL, 87, 0.000);
INSERT INTO `orders` VALUES (5083, 7485, NULL, NULL, 163, 0.000);
INSERT INTO `orders` VALUES (5190, 7485, NULL, NULL, 1, 0.000);
INSERT INTO `orders` VALUES (5396, 7485, NULL, NULL, 12, 0.000);

and my code like this

$outstandings = Orders::select('*')
    ->selectRaw('sum(qty) as qty, sum(pick_qty) as picked_qty, sum(qty) - sum(pick_qty) as outstanding')
    ->groupBy('item_id',  'batch', 'exp_date', 'stock_type_id')
    ->having('outstanding','>' ,'0')
    ->get();
$exept = [];
$temp = [];
$tracking = [];
foreach($outstandings as $outstanding) {
    $storage = Storage::select('storages.*')
    ->where('item_id', $outstanding->item_id)
    ->orderBy('exp_date', 'asc')
    ->orderBy('location_type', 'asc')
    ->orderBy('location_id', 'asc')
    $i = 1;
    $left_qty = 0;
    $left_qty += $outstanding->outstanding;
    while($i <= $outstanding->outstanding) {
        $storage = $storage->whereNotIn('id', $exept)->first();
        if($storage) {
            $left_qty -= $storage->qty;
            if($storage->location_type == 'RACK') {
                if($left_qty < 0) {
                    $temp[$outstanding->item_id.'_'.$outstanding->batch.'_'.$outstanding->exp_date]['locaiton'] = $storage->location->location_id .'-replen';
                    $i += $storage->qty;
                } else {
                    $i += $storage->qty;
                    $exept[] = $storage->id;
                }
            } else {
                if($left_qty < 0) {
                    $i += $storage->qty;
                } else {
                    $exept[] = $storage->id;
                    $i += $storage->qty;
                }
            }
        } else{
            continue;
        }
    }
}

With that code, my result gone wrong, replenish result show 3 of list replenishment, but if i check manually i got result just 2

Result

Source: Ask PHP

LEAVE A COMMENT