cus_wms_supervise-doc/00db/wms_data.sql

-- 1 库存信息
-- ShopStock 表, 不加时间戳, 加监控totalQty的时间字段到ShopStock上
--SELECT * FROM ShopStock WHERE sku='ISZWKJ001930'
--UPDATE ShopStock set pendingShipment=0 WHERE id=6911
-- --增加时间字段
-- ALTER TABLE qhwms.dbo.ShopStock ADD totQtyChgTime DATETIME NULL
-- EXEC sp_addextendedproperty 'MS_Description', '总数变化时间', 'SCHEMA', 'dbo', 'TABLE', 'ShopStock', 'COLUMN', 'totQtyChgTime'
SELECT * from (
      select t1.shortName AS goodsName, t2.customsProductCode AS codeTs, t.sku AS skuNo, t.sku AS goodsId, t1.unit AS gUnitName, t10.codeValue AS gUnit,
          t.totalQty AS gQty, t1.qty1*t.totalQty AS legalQty, t1.unit1 AS legalUnitName, t11.codeValue AS legalUnit,  t1.customsDeclaredElements AS goodsModel,
          t.shopId, t3.compayName AS ownerName, t3.customsCertNo AS ownerCode,
          '440301111758322' AS storeUscCode,  'I440366000315001' AS localEmsNo,
          '深圳前海电商供应链管理有限公司' AS storeCompanyName, '319489717' AS storeUseCode,
          '4403660003' AS storeCustomsCode, '534931948971720171102000281725' AS storeCode, t.totQtyChgTime
      from ShopStock t
          LEFT OUTER JOIN Product t1 ON t.sku=t1.sku
          LEFT OUTER JOIN ProductCategory t2 ON t1.productCategoryId=t2.id
          LEFT OUTER JOIN ShopInfo t3 ON t.shopId=t3.ShopID
          LEFT OUTER JOIN CustomsCode t10 ON t1.unit=t10.codeName AND t10.codeType='4'
          LEFT OUTER JOIN CustomsCode t11 ON t1.unit1=t11.codeName AND t11.codeType='4'
      where t.qualityType='0' AND t.totalQty <> 0 AND t.totQtyChgTime IS NOT NULL
      ) tt
  where tt.totQtyChgTime>='2017-09-27 13:40:12.360'


-- 2 货物上下架信息, 分开报送
-- 1) 上架: AsnItem 表, slottingState 状态:WAREHOUSED, 不加时间戳, 直接使用 warehousedTime 上架时间
-- 2) 下架: (1) 退港: StockWithDrawItem 表, sku, WarehousingEntry 表可用数量必须为0, 不加时间戳, 直接使用 created 创建时间
--          (2) 出货: 建出货流水表记录出货数

-- 上架
--SELECT * FROM AsnItem WHERE sku='ISTTGM000047'
-- 监控数据
SELECT * from (
        SELECT t3.id, t.asnItemId, t1.shortName AS goodsName, t2.customsProductCode AS codeTs, t.sku AS skuNo, t.sku AS goodsId, t1.unit AS gUnitName, t10.codeValue AS gUnit,
            t.nonDefectQty AS gQty, t1.qty1*t.nonDefectQty AS legalQty, t1.unit1 AS legalUnitName, t11.codeValue AS legalUnit, t1.customsDeclaredElements AS goodsModel,
            t4.label AS seatNo, 'I' AS shelfLoadType, t.warehousedTime AS shelfLoadTime,
            '440301111758322' AS storeUscCode, 'I440366000315001' AS localEmsNo,
            '深圳前海电商供应链管理有限公司' AS storeCompanyName, '319489717' AS storeUseCode, '4403660003' AS storeCustomsCode, '534931948971720171102000281725' AS storeCode
        FROM AsnItem t
            LEFT OUTER JOIN Product t1 ON t.sku=t1.sku
            LEFT OUTER JOIN ProductCategory t2 ON t1.productCategoryId=t2.id
            LEFT OUTER JOIN WarehousingEntry t3 ON t.asnItemId=t3.id
            LEFT OUTER JOIN WarehouseSlot t4 ON t3.slotId=t4.id
            LEFT OUTER JOIN CustomsCode t10 ON t1.unit=t10.codeName AND t10.codeType='4'
            LEFT OUTER JOIN CustomsCode t11 ON t1.unit1=t11.codeName AND t11.codeType='4'
    WHERE t.slottingState='WAREHOUSED' AND t3.availableQty  <> 0 AND t.nonDefectQty  <> 0 AND t.warehousedTime IS NOT NULL
              AND t4.label IN (SELECT seat_no AS seatNo FROM WareHouseSlot_cus_data_supervise)
            ) tt
where tt.shelfLoadTime>='2017-04-27 13:40:12.360'


-- (1)退港
-- SELECT * FROM StockWithDrawItem WHERE sku='ISMG660000021'
-- SELECT * FROM WarehousingEntry WHERE id='9968'
-- SELECT * FROM WarehouseSlot WHERE id='16408'
-- 监控数据
SELECT * from (
        SELECT t1.shortName AS goodsName, t2.customsProductCode AS codeTs, t.sku AS skuNo, t.sku AS goodsId, t1.unit AS gUnitName, t10.codeValue AS gUnit,
            t.withdrawQty AS gQty, t1.qty1*t.withdrawQty AS legalQty, t1.unit1 AS legalUnitName, t11.codeValue AS legalUnit, t1.customsDeclaredElements AS goodsModel,
              t4.label AS seatNo, 'E' AS shelfLoadType, t.created AS shelfLoadTime,
            '440301111758322' AS storeUscCode, 'I440366000315001' AS localEmsNo,
              '深圳前海电商供应链管理有限公司' AS storeCompanyName, '319489717' AS storeUseCode, '4403660003' AS storeCustomsCode, '534931948971720171102000281725' AS storeCode
        FROM StockWithdrawItem t
            LEFT OUTER JOIN Product t1 ON t.sku=t1.sku
            LEFT OUTER JOIN ProductCategory t2 ON t1.productCategoryId=t2.id
            LEFT OUTER JOIN WarehousingEntry t3 ON t.warehousingEntryId=t3.id
            LEFT OUTER JOIN WarehouseSlot t4 ON t3.slotId=t4.id
            LEFT OUTER JOIN CustomsCode t10 ON t1.unit=t10.codeName AND t10.codeType='4'
            LEFT OUTER JOIN CustomsCode t11 ON t1.unit1=t11.codeName AND t11.codeType='4'
        WHERE t3.availableQty  <> 0 AND t.created IS NOT NULL
            AND t4.label IN (SELECT seat_no AS seatNo FROM WareHouseSlot_cus_data_supervise)
        ) tt
where tt.shelfLoadTime>='2017-09-27 13:40:12.360'

-- (2) 出货
-- 建出货流水表记录出货数

-- 监控数据
SELECT * from (
            SELECT t.id, t1.shortName AS goodsName, t2.customsProductCode AS codeTs, t.sku AS skuNo, t.sku AS goodsId, t1.unit AS gUnitName, t10.codeValue AS gUnit,
              t.qty AS gQty, t1.qty1*t.qty AS legalQty, t1.unit1 AS legalUnitName, t11.codeValue AS legalUnit, t1.customsDeclaredElements AS goodsModel,
              t4.label AS seatNo, 'E' AS shelfLoadType, t.createTime AS shelfLoadTime,
              '440301111758322' AS storeUscCode,  'I440366000315001' AS localEmsNo,
              '深圳前海电商供应链管理有限公司' AS storeCompanyName, '319489717' AS storeUseCode, '4403660003' AS storeCustomsCode, '534931948971720171102000281725' AS storeCode
            FROM WarehousingEntry_shipment t
                LEFT OUTER JOIN Product t1 ON t.sku=t1.sku
                LEFT OUTER JOIN ProductCategory t2 ON t1.productCategoryId=t2.id
                LEFT OUTER JOIN WarehouseSlot t4 ON t.slotId=t4.id
                LEFT OUTER JOIN CustomsCode t10 ON t1.unit=t10.codeName AND t10.codeType='4'
                LEFT OUTER JOIN CustomsCode t11 ON t1.unit1=t11.codeName AND t11.codeType='4'
            WHERE t.qty <> 0 AND t.createTime IS NOT NULL
              AND t4.label IN (SELECT seat_no AS seatNo FROM WareHouseSlot_cus_data_supervise)
        ) tt
where tt.shelfLoadTime>='2017-09-27 13:40:12.360'


-- 3 货物库位移动信息
-- StockSlotTransfer 表, 货物库位移动
-- state, 0:未移仓;1已移仓
-- qualityType, 0:良，1:次
-- 下架时间与上架时间一致
-- SELECT * FROM StockSlotTransfer WHERE sku='ISTTGM000047'
-- SELECT * FROM WarehouseSlot WHERE id='15817'
-- --不加时间戳, 直接使用 transferTime 转移时间, transferTime不为空, state='1', qualityType='0'
-- 监控数据
SELECT * from (
	SELECT t.transferTime AS shelfLoadTime, t.transferTime AS shelfUnloadTime, t5.label AS targetSeatNo, t4.label AS originSeatNo,
		t1.shortName AS goodsName, t2.customsProductCode AS codeTs, t.sku AS skuNo, t.sku AS goodsId, t1.unit AS gUnitName, t10.codeValue AS gUnit, t.qty AS gQty,
		t1.qty1*t.qty AS legalQty, t1.unit1 AS legalUnitName, t11.codeValue AS legalUnit, t1.customsDeclaredElements AS goodsModel,
		'440301111758322' AS storeUscCode, 'I440366000315001' AS localEmsNo,
	   '深圳前海电商供应链管理有限公司' AS storeCompanyName, '319489717' AS storeUseCode, '4403660003' AS storeCustomsCode, '534931948971720171102000281725' AS storeCode,
		transferTime
	FROM StockSlotTransfer t
		LEFT OUTER JOIN Product t1 ON t.sku=t1.sku
		LEFT OUTER JOIN ProductCategory t2 ON t1.productCategoryId=t2.id
		LEFT OUTER JOIN WarehouseSlot t4 ON t.sourceSlotId=t4.id
		LEFT OUTER JOIN WarehouseSlot t5 ON t.destSlotId=t5.id
		LEFT OUTER JOIN CustomsCode t10 ON t1.unit=t10.codeName AND t10.codeType='4'
		LEFT OUTER JOIN CustomsCode t11 ON t1.unit1=t11.codeName AND t11.codeType='4'
	WHERE t.state='1' AND t.qualityType='0' AND t.qty &lt;&gt; 0 AND t.transferTime IS NOT NULL
		AND t4.label IN (SELECT seat_no AS seatNo FROM WareHouseSlot_cus_data_supervise)
		AND t5.label IN (SELECT seat_no AS seatNo FROM WareHouseSlot_cus_data_supervise)
	) tt
where tt.shelfLoadTime>='2017-08-24 11:36:11.317'

-- 4 库位货物信息
-- 1. .WarehousingEntry 表, 库位, 2.WarehouseSlot 标签
-- 不增加时间戳,
-- WarehousingEntryMapper.xml addQtyById, updateTBNum, updateQtyById 增加时间监管可用数量字段变化
-- WarehousingEntryMapper.java batchUpdateQty
-- ISTH01001215
-- type, 0:良，1:次
-- SELECT * FROM WarehousingEntry WHERE sku='ISTH01001215'
-- SELECT * FROM WarehouseSlot WHERE id='15892'
-- --增加时间字段
-- ALTER TABLE qhwms.dbo.WarehousingEntry ADD avaQtyChgTime DATETIME NULL
-- EXEC sp_addextendedproperty 'MS_Description', '可用数量变化时间', 'SCHEMA', 'dbo', 'TABLE', 'WarehousingEntry', 'COLUMN', 'avaQtyChgTime'
-- 监控数据
SELECT * from (
        SELECT t4.label AS seatNo, t1.shortName AS goodsName, t2.customsProductCode AS codeTs, t.sku AS skuNo, t.sku AS goodsId, t1.unit AS gUnitName, t10.codeValue AS gUnit,
            (t.availableQty + t.frozenQty) AS gQty, t1.qty1*(t.availableQty + t.frozenQty) AS legalQty, t1.unit1 AS legalUnitName, t11.codeValue AS legalUnit, t1.customsDeclaredElements AS goodsModel,
              t.shopId, t3.compayName AS ownerName, t3.customsCertNo AS ownerCode,
            '440301111758322' AS storeUscCode, 'I440366000315001' AS localEmsNo,
              '深圳前海电商供应链管理有限公司' AS storeCompanyName, '319489717' AS storeUseCode, '4403660003' AS storeCustomsCode, '534931948971720171102000281725' AS storeCode,
            t.avaQtyChgTime
        FROM WarehousingEntry t
            LEFT OUTER JOIN Product t1 ON t.sku=t1.sku
            LEFT OUTER JOIN ProductCategory t2 ON t1.productCategoryId=t2.id
            LEFT OUTER JOIN ShopInfo t3 ON t.shopId=t3.ShopId
            LEFT OUTER JOIN WarehouseSlot t4 ON t.slotId=t4.id
            LEFT OUTER JOIN CustomsCode t10 ON t1.unit=t10.codeName AND t10.codeType='4'
            LEFT OUTER JOIN CustomsCode t11 ON t1.unit1=t11.codeName AND t11.codeType='4'
         WHERE t.type='0' AND t.availableQty &lt;&gt; 0 AND t.avaQtyChgTime IS NOT NULL
            AND t4.label IN (SELECT seat_no AS seatNo FROM WareHouseSlot_cus_data_supervise)
        ) tt
where tt.avaQtyChgTime>='2017-09-27 13:40:12.360'


-- 4 增加正常库位表
-- 目前正在使用中正常库位信息
-- 共 16508
-- 正常状态 16473
-- 冻结状态    35

-- 使用中 5736
-- 对使用中的库位建表


------------- 建表开始 -------------
USE [qhwms]
GO

-- 仓库出货流水,每次写入一条

CREATE TABLE qhwms.dbo.WarehousingEntry_shipment
(
    id BIGINT PRIMARY KEY NOT NULL IDENTITY,
    warehousingEntryId INT,
    sku VARCHAR(50),
    qty INT,
    slotId INT,
    createTime DATETIME,
    shopId INT
)

/****** Object:  Table [dbo].[WarehouseSlot_cus_data_supervise]    Script Date: 11/11/2017 11:01:48 ******/

-- 正常库位表

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[WarehouseSlot_cus_data_supervise](
	[seat_no] [varchar](32) NULL,
	[is_valid] [char](1) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'库位编码，对应WarehouseSlotge表label' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'WarehouseSlot_cus_data_supervise', @level2type=N'COLUMN',@level2name=N'seat_no'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否可用' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'WarehouseSlot_cus_data_supervise', @level2type=N'COLUMN',@level2name=N'is_valid'
GO
ALTER TABLE [dbo].[WarehouseSlot_cus_data_supervise] ADD  CONSTRAINT [DF_WarehouseSlot_cus_data_supervise_is_valid]  DEFAULT ((1)) FOR [is_valid]
GO

------------- 建表结束 -------------