Trouble with logic stock take

A

Anonymous

Guest
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
Cuplikan layar 2021-10-16 040715.png
Storage Data
Cuplikan layar 2021-10-16 040726.png

Logic
1. Storage Must ordered 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

the expected result like this



Code: 
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
Code: 
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, 1, NULL, NULL, 25, 0.000);
INSERT INTO `orders` VALUES (5077, 1, NULL, NULL, 87, 0.000);
INSERT INTO `orders` VALUES (5083, 1, NULL, NULL, 163, 0.000);
INSERT INTO `orders` VALUES (5190, 1, NULL, NULL, 1, 0.000);
INSERT INTO `orders` VALUES (5396, 1, NULL, NULL, 12, 0.000);
and my code like this
Code: 
$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')
    ->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
 

Attachments

  • Cuplikan layar 2021-10-16 040741.png
    Cuplikan layar 2021-10-16 040741.png
    21.3 KB · Views: 2,134
You must log in or register to reply here.
Back
Top