with ware_class AS(
SELECT
tp.Code,
GROUP_CONCAT(tm.Name ORDER BY tm.Level ASC SEPARATOR '/') AS classname
FROM
b2b_oms.tpro_mallcategory tm
LEFT JOIN
b2b_oms.tpro_productmallcategory tpm ON tm.Id = tpm.mallcategoryId
LEFT JOIN
b2b_oms.tpro_product tp ON tpm.ProductId = tp.Id
WHERE
tpm.AreaId = 3286299645965762560 AND tp.IsDeleted = 0 AND tm.IsDeleted = 0 AND tpm.IsDeleted = 0
GROUP BY
tp.Code)
with sales_30 AS(
SELECT
f.`SkuCode` AS '商品编码',
SUM(b.`Qty`) AS '出库30总数量'
FROM
b2b_wms.st_outstockorder a
LEFT JOIN b2b_wms.st_outstockorderitem b ON a.`Id` = b.`OutStockOrderId`
LEFT JOIN b2b_wms.st_warehouse c ON a.`WarehouseId` = c.`Id`
LEFT JOIN b2b_wms.st_warefield d ON b.`WareFieldId` = d.`Id`
LEFT JOIN b2b_wms.st_wareposition e ON b.`WarePositionId` = e.`Id`
LEFT JOIN b2b_wms.st_wares f ON b.`WaresId` = f.`Id`
LEFT JOIN b2b_wms.st_userprofile g ON a.`Creator` = g.`UserName`
LEFT JOIN b2b_wms.st_outstockorderitemhis h ON b.`Id` = h.`OutStockOrderItemId`
AND a.`Id` = h.`OutStockOrderId`
LEFT JOIN b2b_wms.st_userprofile i ON h.`ReviewUser` = i.`UserName`
WHERE
a.`TenantId` IN (
'3286299645965762560',
'3131897506372599808'
)
AND a.`WarehouseId` IN (
'3370028676427628544',
'3353714265942151168'
)
AND a.`Status` = 3
AND a.`Type` = 1
AND d.X4 = 0
AND a.`CreateTime` >= DATE_SUB(NOW(),INTERVAL 30 DAY)
GROUP BY f.`SkuCode`
)
WITH sales_90 AS(
SELECT
f.`SkuCode` AS '商品编码',
SUM(b.`Qty`) AS '出库90总数量'
FROM
b2b_wms.st_outstockorder a
LEFT JOIN b2b_wms.st_outstockorderitem b ON a.`Id` = b.`OutStockOrderId`
LEFT JOIN b2b_wms.st_warehouse c ON a.`WarehouseId` = c.`Id`
LEFT JOIN b2b_wms.st_warefield d ON b.`WareFieldId` = d.`Id`
LEFT JOIN b2b_wms.st_wareposition e ON b.`WarePositionId` = e.`Id`
LEFT JOIN b2b_wms.st_wares f ON b.`WaresId` = f.`Id`
LEFT JOIN b2b_wms.st_userprofile g ON a.`Creator` = g.`UserName`
LEFT JOIN b2b_wms.st_outstockorderitemhis h ON b.`Id` = h.`OutStockOrderItemId`
AND a.`Id` = h.`OutStockOrderId`
LEFT JOIN b2b_wms.st_userprofile i ON h.`ReviewUser` = i.`UserName`
WHERE
a.`TenantId` IN (
'3286299645965762560',
'3131897506372599808'
)
AND a.`WarehouseId` IN (
'3370028676427628544',
'3353714265942151168'
)
AND a.`Status` = 3
AND a.`Type` = 1
AND d.X4 = 0
AND a.`CreateTime` >= DATE_SUB(NOW(),INTERVAL 90 DAY)
GROUP BY f.`SkuCode`
)
SELECT t.'仓库名称', t.'库区', t.'库位', t.'商品编码', t.'商品名称', t.'商品条码', t.'单位', t.'规格',wc.classname AS '商品分类', SUM(t.'库存数量') AS '库存总数量',
(SUM(t.'库存数量') / ((w3.'出库30总数量' + w9.'出库90总数量') / 2) * 30) AS '周转天数',
SUM(t.'库存金额') AS '库存总金额', t.'分拣类型'
FROM (
SELECT
CONCAT(c.`Name`, c.`Code`) AS '仓库名称', CASE
WHEN e.`X4` = 0 THEN CONCAT('分拣区', e.`Code`)
ELSE '其他'
END AS '库区', d.`Code` AS '库位', f.`SkuCode` AS '商品编码', f.`SkuName` AS '商品名称', f.`Barcode` AS '商品条码', f.`Unit` AS '单位', SUBSTRING(f.`Subtitle`, 1, 5) AS '规格', b.`Qty` AS '库存数量', b.`Amount` AS '库存金额', CASE
WHEN a.`FnType` = 3 THEN '拆零'
WHEN a.`FnType` = 1 THEN '整件'
ELSE '其他'
END AS '分拣类型'
FROM
b2b_wms.st_warestore a
LEFT JOIN b2b_wms.st_warestoreitem b ON a.Id = b.`WareStoreId`
LEFT JOIN b2b_wms.st_warehouse c ON a.`WarehouseId` = c.`Id`
LEFT JOIN b2b_wms.st_wareposition d ON b.`WarePositionId` = d.`Id`
LEFT JOIN b2b_wms.st_warefield e ON d.`WareFieldId` = e.`Id`
AND d.`WarehouseId` = e.`WarehouseId`
LEFT JOIN b2b_wms.st_wares f ON a.`WaresId` = f.`Id`
WHERE
a.`TenantId` IN (
'3286299645965762560', '3131897506372599808'
)
AND a.`WarehouseId` IN (
'3370028676427628544', '3353714265942151168'
)
AND b.`Qty` > 0
AND e.`X4` = 0
) t
LEFT JOIN ware_class wc ON t.'商品编码' = wc.CODE
LEFT JOIN wares_30 w3 ON t.'商品编码' = w3.'商品编码'
LEFT JOIN wares_90 w9 ON t.'商品编码' = w9.'商品编码'
GROUP BY t.'仓库名称', t.'库区', t.'库位', t.'商品编码', t.'商品名称', t.'商品条码', t.'单位', t.'规格', wc.classname, t.'分拣类型'