库存报表

最新推荐文章于 2024-05-15 09:16:03 发布

lee_126

最新推荐文章于 2024-05-15 09:16:03 发布

阅读量1.9k

点赞数

分类专栏：工作笔记

本文链接：https://blog.csdn.net/lily_better/article/details/101379580

版权

工作笔记专栏收录该内容

13 篇文章 4 订阅

订阅专栏

最近接手WMS系统库存统计的工作，整理了一下WM现有的库存统计方式。

1 库存报表相关的表

1.1 库存表warehouse_inventory

按照仓库+货主+库位+sku+品质+批次的维度，记录每个sku的实时库存：总库存、可用库存、占用库存、冻结库存。

（当SKU品质是次品时，库存存放在冻结库存；良品则存放在可用库存。）

CREATE TABLE `wms`.`warehouse_inventory` (
`id` bigint(64) NOT NULL COMMENT '库存表',
`store_id` bigint(64) NULL DEFAULT NULL COMMENT '仓库id',
`location_id` bigint(64) NULL DEFAULT NULL COMMENT '库位id',
`sku_id` bigint(64) NULL DEFAULT NULL COMMENT 'skuid',
`total_store` decimal(12,2) NULL DEFAULT NULL COMMENT '总共库存',
`apply_store` decimal(12,2) NULL DEFAULT NULL COMMENT '可用库存',
`occ_store` decimal(12,2) NULL DEFAULT NULL COMMENT '占用库存',
`frozen_store` decimal(12,2) NULL DEFAULT NULL COMMENT '冻结库存',
`customer_id` bigint(64) NULL DEFAULT NULL COMMENT '货主id',
`quality_type` tinyint(1) NULL DEFAULT NULL COMMENT '质量状态',
`batch_no` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '批次号',
`create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`create_user` bigint(64) NULL DEFAULT NULL,
`update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
`update_user` bigint(64) NULL DEFAULT NULL,
PRIMARY KEY (`id`) 
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_general_ci;

1.2 库存日志表warehouse_inventory_log

记录操作库存的变化，4种库存*3种变化=12。

4种库存：总库存、可用库存、占用库存、冻结库存；

3种变化：变化前、变化、变化后。

当涉及到库存变化操作时，例如上架、入库、出库、占用库存、释放库存、确认拣货移到过渡库位、盘盈/盘亏，都需要记录库存日志。

按照货主+仓库+sku维度，记录每个SKU的四种库存的三种数量，还记录了操作类型及涉及的单据。

CREATE TABLE `wms`.`warehouse_inventory_log` (
`id` bigint(64) NOT NULL COMMENT '库存日志表',
`customer_id` bigint(64) NULL DEFAULT NULL COMMENT '货主id',
`customer_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '货主姓名',
`store_id` bigint(64) NULL DEFAULT NULL COMMENT '仓库id',
`store_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '仓库名称',
`type` tinyint(1) NULL DEFAULT NULL COMMENT '操作类型（1:增加、0:减少，2:不变）WarehouseInventoryLogTypeEnum',
`change_before_total` decimal(12,2) NULL DEFAULT NULL COMMENT '变化前总库存',
`change_after_total` decimal(12,2) NULL DEFAULT NULL COMMENT '变化后总库存',
`change_count_total` decimal(12,2) NULL DEFAULT NULL COMMENT '变化总库存',
`change_before_apply` decimal(12,2) NULL DEFAULT NULL COMMENT '变化前可用库存',
`change_after_apply` decimal(12,2) NULL DEFAULT NULL COMMENT '变化后可用库存',
`change_count_apply` decimal(12,2) NULL DEFAULT NULL COMMENT '变化可用库存',
`change_before_occ` decimal(12,2) NULL DEFAULT NULL COMMENT '变化前用库存',
`change_after_occ` decimal(12,2) NULL DEFAULT NULL COMMENT '变化后占用库存',
`change_count_occ` decimal(12,2) NULL DEFAULT NULL COMMENT '变化占用库存',
`change_before_frozen` decimal(12,2) NULL DEFAULT NULL COMMENT '变化前冻结库存',
`change_after_frozen` decimal(12,2) NULL DEFAULT NULL COMMENT '变化后冻结库存',
`change_count_frozen` decimal(12,2) NULL DEFAULT NULL COMMENT '变化冻结库存',
`sku_id` bigint(64) NULL DEFAULT NULL COMMENT 'sku',
`account_type` tinyint(1) NULL DEFAULT NULL COMMENT '业务类型 WarehouseInventoryLogAccountTypeEnum',
`account_id` bigint(64) NULL DEFAULT NULL COMMENT '业务住建',
`create_time` timestamp NULL DEFAULT NULL,
`order_create_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`) 
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_general_ci;

操作类型account_type：

public enum WarehouseInventoryLogAccountTypeEnum {
    DEMO(0,"入库单"),
    INVOICE(1,"发运订单"),
    ASSIGNPICK(2,"捡货单占用库存"),
    RELEASEPICK(3,"捡货单释放库存"),
    RECEIVE_FREEZE(4,"收货冻结暂存区"),
    ONSHELF(5,"上架单"),
    RECEIVE_UNFROZEN(6,"收货解冻暂存区"),
    RECEIVE_CANCEL(7,"取消收货"),
    CHECK_DIFF(8,"盘点单"),;
}

操作类型type: 1- 增加，0-减少，2-不变。

type=1的操作有：采购入库，收货冻结暂存区，盘盈

type=0的操作有：发运交接，盘亏

type=2的操作：库内操作，例如占用库存，释放库存

1.3 库存报表warehouse_inventory_report

按照仓库+货主+sku的维度，记录每个SKU的期初库存、入库数、出库数、结存数。

report_time是统计时间，现在WMS每天记录库存数据。

CREATE TABLE `wms`.`warehouse_inventory_report` (
`id` bigint(64) NOT NULL COMMENT '库存报表',
`store_id` bigint(64) NULL DEFAULT NULL COMMENT '仓库id',
`customer_id` bigint(64) NULL DEFAULT NULL COMMENT '货主id',
`sku_id` bigint(64) NULL DEFAULT NULL COMMENT 'skuid',
`category_id` bigint(64) NULL DEFAULT NULL COMMENT '分类id',
`start_num` int(12) NOT NULL COMMENT '期初库存',
`add_num` int(12) NULL DEFAULT NULL COMMENT '入库',
`sub_num` int(12) NULL DEFAULT NULL COMMENT '出库',
`end_num` int(12) NULL DEFAULT NULL COMMENT '结存',
`report_time` datetime NULL DEFAULT NULL COMMENT '报表时间',
`create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`) 
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_general_ci;

2. 库存统计

2.1 两种统计方式

第一种、定时任务：每天凌晨一点执行定时任务，统计前一天的SKU期初、入库、出库、结存数量。

删除历史数据，重新统计。

第二种手动执行任务。输入日期，例如输入2019-10-10，或者2019-10-10,2019-10-15，统计指定日期的SKU期初、入库、出库、结存数量。

统计后的数据保存在warehouse_inventory_report。

这样库存报表直接查询warehouse_inventory_report，提升报表的加载速度。

2.2 从库存日志表warehouse_inventory_log查询出一天的期初、入库、出库、结存。

不统计订单取消的出入库数量。

因为入库有取消功能，（出库单没有取消），所以这里account_type=7，是入库取消的库存日志，

当account_type=4采购入库时，需要判断对应的入库单是否删除了，删除了不能统计。

统计2019-09-21这天的数据。

 SELECT `store_id`,`customer_id`,`sku_id`,
        SUM(IF (t.type=1, t.change_count_total,0) ) AS `add_num`,
        SUM(IF (t.type=0, t.change_count_total,0) ) AS `sub_num`,
        SUBSTRING_INDEX(GROUP_CONCAT( change_before_total ORDER BY order_create_time,id), ',',1) AS start_num,
        SUBSTRING_INDEX(GROUP_CONCAT( change_after_total ORDER BY order_create_time,id), ',',-1) AS end_num,
        `order_create_time` AS create_time
        FROM
        (SELECT *  FROM warehouse_inventory_log
        where
            type in (0,1) and account_type!=7
            and id in (
            select id from warehouse_inventory_log where account_type=4 and EXISTS(select 1 from receive_main where id=account_id and deleted=0 )
            union
            select id  from warehouse_inventory_log where account_type!=4
            )
            and order_create_time >= '2019-09-21 00:00:00' and order_create_time <= '2019-09-21 23:59:59'
        ORDER BY order_create_time) t
        GROUP BY  store_id, sku_id,customer_id

2.3 将2.1查询出来的结果，保存到库存报表warehouse_inventory_report。

3、报表展示

3.1 即时库存报表

后台sql查询：

SELECT
`id`,
`store_id`,
`location_id`,
`sku_id`,
ifnull( sum( `total_store` ), 0 ) total_store,
ifnull( sum( `apply_store` ), 0 ) apply_store,
ifnull( sum( `occ_store` ), 0 ) occ_store,
ifnull( sum( `frozen_store` ), 0 ) frozen_store,
`customer_id`,
`quality_type`,
`batch_no`,
`create_time`,
`create_user`,
`update_time`,
`update_user` 
FROM
	warehouse_inventory 
WHERE
	store_id = 1 
	AND total_store > 0 
GROUP BY
	store_id,
	customer_id,
	sku_id 
ORDER BY
	store_id,
	customer_id 
	LIMIT 20

3.2 选中一条即时库存，点击“查看详情”

后台sql查询：

SELECT
	`id`,
	`store_id`,
	`location_id`,
	`sku_id`,
	ifnull( sum( `total_store` ), 0 ) total_store,
	ifnull( sum( `apply_store` ), 0 ) apply_store,
	ifnull( sum( `occ_store` ), 0 ) occ_store,
	ifnull( sum( `frozen_store` ), 0 ) frozen_store,
	`customer_id`,
	`quality_type`,
	`batch_no`,
	`create_time`,
	`create_user`,
	`update_time`,
	`update_user` 
FROM
	warehouse_inventory 
WHERE
	store_id = 1 
	AND sku_id = 370495141103906816 
	AND customer_id = 353104904627421184 
GROUP BY
	location_id,
	quality_type 
ORDER BY
	store_id,
	customer_id 
	LIMIT 20;

3.3 收发汇总表查询

后台sql查询：

SELECT
	`id`,
	`store_id`,
	`customer_id`,
	`sku_id`,
	SUM( add_num ) AS `add_num`,
	SUM( `sub_num` ) AS sub_num,
	SUBSTRING_INDEX( GROUP_CONCAT( start_num ORDER BY report_time ), ',', 1 ) AS start_num,
	SUBSTRING_INDEX( GROUP_CONCAT( end_num ORDER BY report_time ), ',',- 1 ) AS end_num,
	`report_time`,
	`create_time` 
FROM
	( SELECT * FROM warehouse_inventory_report WHERE store_id = 393791072410554368 AND report_time >= '2019-7-26' AND report_time <= '2019-10-24' ORDER BY report_time ) a 
GROUP BY
	customer_id,
	store_id,
	sku_id 
	LIMIT 20;

统计一个仓库下，某个货主，‘2019-7-26’和‘2019-10-24’这段时间的所有sku的期初、入库、出库、结存数。

期初：该sku的第一条库存报表的期初值。

入库：该sku的库存报表所有入库数之和。

出库：该sku的库存报表所有出库数之和。

结存：该sku的最后一条库存报表的结存值。

最后期望结果期初+入库-出库=结存。

3.4 收发汇总明细查询。

选中收货汇总的一条记录，点击明细表，就可以查询所有的出入明细记录。

入库数：11；出库数：3.

后台sql查询：

SELECT
	DATE_FORMAT( occTime, '%Y-%m-%d %H:%i:%S' ) occTime,
	temp.`store_id` AS storeId,
	temp.`account_id` AS accountId,
	accountType,
	inOrOut,
	wmsBillNo,
	cusBillNo,
IF
	( cusBillNo IS NULL OR cusBillNo = '', wmsBillNo, cusBillNo ) orderNo,
	wlog.`sku_id` AS skuId,
	wlog.`change_count_total` AS packageCount,
	temp.type type 
FROM
	( SELECT customer_id, store_id, account_id, account_type, type FROM warehouse_inventory_log l GROUP BY customer_id, store_id, account_id, account_type, type ) temp
	INNER JOIN warehouse_inventory_log wlog ON temp.account_id = wlog.`account_id` 
	AND temp.account_type = wlog.`account_type`
	INNER JOIN (
	SELECT
		t1.gmt_create occTime,
		t1.id accountId,
		'收' inOrOut,
		( CASE asn_main.type WHEN 0 THEN '采购入库' WHEN 1 THEN '生产入库' WHEN 2 THEN '销退入库' WHEN 3 THEN '调拨入库' ELSE '其他' END ) accountType,
		asn_main.NO wmsBillNo,
		asn_main.customer_order_no AS cusBillNo 
	FROM
		receive_main t1
		INNER JOIN asn_main ON asn_main.id = t1.asn_id 
	WHERE
		asn_main.deleted = 0 
		AND t1.deleted = 0 
		AND t1.id IN ( SELECT account_id FROM warehouse_inventory_log t2 WHERE account_type = 4 ) UNION
	SELECT
		t1.gmt_create occTime,
		t1.id accountId,
		'发' inOrOut,
		( CASE t1.business_type WHEN 0 THEN '销售出库' WHEN 1 THEN '仓间调拨' WHEN 2 THEN '退供应商' ELSE '其他' END ) accountType,
		t1.order_no wmsBillNo,
		t1.customer_no cusBillNo 
	FROM
		send_order_main t1 
	WHERE
		t1.deleted = 0 UNION
	SELECT
		t1.create_time occTime,
		t1.id accountId,
		( CASE t1.diff_type WHEN 0 THEN '发' WHEN 1 THEN '收' ELSE '其他' END ) inOrOut,
		( CASE t1.diff_type WHEN 0 THEN '盘亏' WHEN 1 THEN '盘盈' ELSE '其他' END ) accountType,
		t1.check_diff_no wmsBillNo,
		'' cusBillNo 
	FROM
		check_diff t1 
	WHERE
		t1.deleted = 0 
	AND t1.id IN ( SELECT account_id FROM warehouse_inventory_log t2 )) temp1 ON temp1.accountId = wlog.account_id 
WHERE
	temp.type IN ( 0, 1 ) 
	AND temp.`store_id` = 1 
	AND sku_id IN ( 370495141103906816 ) 
	AND wlog.customer_id = 353104904627421184 
	AND wlog.order_create_time >= '2019-07-26 00:00:00' 
	AND wlog.order_create_time <= '2019-10-24 23:59:59' 
ORDER BY
	occTime DESC,
	orderNo 
	LIMIT 20;

后面要过滤掉已经取消的订单。。。