Implement API security baseline (sys_user, JWT, audit fields, transactions, unique constraints) and Web real login with 401 handling. Document setup and development workflows in root, WmsLite.Api, and WmsLite.Web README files. Co-authored-by: Cursor <cursoragent@cursor.com>
182 lines
9.7 KiB
C#
182 lines
9.7 KiB
C#
using FluentMigrator;
|
|
|
|
namespace WmsLite.Api.Database.Migrations;
|
|
|
|
[Migration(2, "Add order tables, common indexes, and seed data")]
|
|
public class AddOrdersIndexesAndSeedData0002 : Migration
|
|
{
|
|
public override void Up()
|
|
{
|
|
CreateOrderTables();
|
|
CreateIndexes();
|
|
SeedDemoData();
|
|
}
|
|
|
|
public override void Down()
|
|
{
|
|
Execute.Sql("DELETE FROM sys_template WHERE tenant_id = 'default' AND owner_code = 'default';");
|
|
Execute.Sql("DELETE FROM location WHERE tenant_id = 'default' AND owner_code = 'default';");
|
|
Execute.Sql("DELETE FROM zone WHERE tenant_id = 'default' AND owner_code = 'default';");
|
|
Execute.Sql("DELETE FROM warehouse WHERE tenant_id = 'default' AND owner_code = 'default';");
|
|
|
|
Delete.Table("stocktake_order_line");
|
|
Delete.Table("stocktake_order");
|
|
Delete.Table("transfer_order_line");
|
|
Delete.Table("transfer_order");
|
|
Delete.Table("outbound_order_line");
|
|
Delete.Table("outbound_order");
|
|
Delete.Table("inbound_order_line");
|
|
Delete.Table("inbound_order");
|
|
}
|
|
|
|
private void CreateOrderTables()
|
|
{
|
|
CreateOrderHeader("inbound_order");
|
|
CreateInventoryOrderLine("inbound_order_line");
|
|
CreateOrderHeader("outbound_order");
|
|
CreateInventoryOrderLine("outbound_order_line");
|
|
CreateTransferOrder();
|
|
CreateTransferOrderLine();
|
|
CreateOrderHeader("stocktake_order");
|
|
CreateStocktakeOrderLine();
|
|
}
|
|
|
|
private void CreateOrderHeader(string tableName)
|
|
{
|
|
if (Schema.Table(tableName).Exists())
|
|
{
|
|
return;
|
|
}
|
|
|
|
Create.Table(tableName)
|
|
.WithBaseColumns()
|
|
.WithColumn("order_no").AsString(128).NotNullable()
|
|
.WithColumn("biz_type").AsString(64).Nullable()
|
|
.WithColumn("order_status").AsInt32().NotNullable()
|
|
.WithColumn("remark").AsString(512).Nullable()
|
|
.WithColumn("attrs").AsCustom("jsonb").Nullable();
|
|
}
|
|
|
|
private void CreateInventoryOrderLine(string tableName)
|
|
{
|
|
if (Schema.Table(tableName).Exists())
|
|
{
|
|
return;
|
|
}
|
|
|
|
Create.Table(tableName)
|
|
.WithBaseColumns()
|
|
.WithColumn("order_id").AsInt64().NotNullable()
|
|
.WithColumn("line_no").AsInt32().NotNullable()
|
|
.WithColumn("inventory_kind").AsInt32().NotNullable()
|
|
.WithColumn("material_code").AsString(64).Nullable()
|
|
.WithColumn("container_code").AsString(64).Nullable()
|
|
.WithColumn("warehouse_code").AsString(64).NotNullable()
|
|
.WithColumn("zone_code").AsString(64).NotNullable()
|
|
.WithColumn("location_code").AsString(64).NotNullable()
|
|
.WithColumn("qty").AsDecimal().NotNullable()
|
|
.WithColumn("unit").AsString(32).NotNullable()
|
|
.WithColumn("attrs").AsCustom("jsonb").Nullable();
|
|
}
|
|
|
|
private void CreateTransferOrder()
|
|
{
|
|
CreateOrderHeader("transfer_order");
|
|
}
|
|
|
|
private void CreateTransferOrderLine()
|
|
{
|
|
if (Schema.Table("transfer_order_line").Exists())
|
|
{
|
|
return;
|
|
}
|
|
|
|
Create.Table("transfer_order_line")
|
|
.WithBaseColumns()
|
|
.WithColumn("order_id").AsInt64().NotNullable()
|
|
.WithColumn("line_no").AsInt32().NotNullable()
|
|
.WithColumn("inventory_kind").AsInt32().NotNullable()
|
|
.WithColumn("material_code").AsString(64).Nullable()
|
|
.WithColumn("container_code").AsString(64).Nullable()
|
|
.WithColumn("from_warehouse_code").AsString(64).NotNullable()
|
|
.WithColumn("from_zone_code").AsString(64).NotNullable()
|
|
.WithColumn("from_location_code").AsString(64).NotNullable()
|
|
.WithColumn("to_warehouse_code").AsString(64).NotNullable()
|
|
.WithColumn("to_zone_code").AsString(64).NotNullable()
|
|
.WithColumn("to_location_code").AsString(64).NotNullable()
|
|
.WithColumn("qty").AsDecimal().NotNullable()
|
|
.WithColumn("unit").AsString(32).NotNullable()
|
|
.WithColumn("attrs").AsCustom("jsonb").Nullable();
|
|
}
|
|
|
|
private void CreateStocktakeOrderLine()
|
|
{
|
|
if (Schema.Table("stocktake_order_line").Exists())
|
|
{
|
|
return;
|
|
}
|
|
|
|
Create.Table("stocktake_order_line")
|
|
.WithBaseColumns()
|
|
.WithColumn("order_id").AsInt64().NotNullable()
|
|
.WithColumn("line_no").AsInt32().NotNullable()
|
|
.WithColumn("inventory_kind").AsInt32().NotNullable()
|
|
.WithColumn("material_code").AsString(64).Nullable()
|
|
.WithColumn("container_code").AsString(64).Nullable()
|
|
.WithColumn("warehouse_code").AsString(64).NotNullable()
|
|
.WithColumn("zone_code").AsString(64).NotNullable()
|
|
.WithColumn("location_code").AsString(64).NotNullable()
|
|
.WithColumn("qty").AsDecimal().NotNullable()
|
|
.WithColumn("counted_qty").AsDecimal().NotNullable()
|
|
.WithColumn("unit").AsString(32).NotNullable()
|
|
.WithColumn("attrs").AsCustom("jsonb").Nullable();
|
|
}
|
|
|
|
private void CreateIndexes()
|
|
{
|
|
Execute.Sql("""
|
|
CREATE INDEX IF NOT EXISTS idx_warehouse_tenant_owner_code ON warehouse(tenant_id, owner_code, code);
|
|
CREATE INDEX IF NOT EXISTS idx_zone_tenant_owner_warehouse_code ON zone(tenant_id, owner_code, warehouse_code, code);
|
|
CREATE INDEX IF NOT EXISTS idx_location_tenant_owner_wzl_code ON location(tenant_id, owner_code, warehouse_code, zone_code, code);
|
|
CREATE INDEX IF NOT EXISTS idx_material_tenant_owner_code ON material(tenant_id, owner_code, code);
|
|
CREATE INDEX IF NOT EXISTS idx_container_tenant_owner_code ON container(tenant_id, owner_code, code);
|
|
CREATE INDEX IF NOT EXISTS idx_inventory_tenant_owner_location ON inventory(tenant_id, owner_code, warehouse_code, zone_code, location_code);
|
|
CREATE INDEX IF NOT EXISTS idx_inventory_tenant_owner_material ON inventory(tenant_id, owner_code, material_code);
|
|
CREATE INDEX IF NOT EXISTS idx_inventory_tenant_owner_container ON inventory(tenant_id, owner_code, container_code);
|
|
CREATE INDEX IF NOT EXISTS idx_inventory_attrs_gin ON inventory USING GIN(attrs);
|
|
CREATE INDEX IF NOT EXISTS idx_material_attrs_gin ON material USING GIN(attrs);
|
|
CREATE INDEX IF NOT EXISTS idx_container_attrs_gin ON container USING GIN(attrs);
|
|
CREATE INDEX IF NOT EXISTS idx_sys_template_tenant_owner_biz_type ON sys_template(tenant_id, owner_code, biz_type);
|
|
CREATE INDEX IF NOT EXISTS idx_inbound_order_tenant_owner_order_no ON inbound_order(tenant_id, owner_code, order_no);
|
|
CREATE INDEX IF NOT EXISTS idx_outbound_order_tenant_owner_order_no ON outbound_order(tenant_id, owner_code, order_no);
|
|
CREATE INDEX IF NOT EXISTS idx_transfer_order_tenant_owner_order_no ON transfer_order(tenant_id, owner_code, order_no);
|
|
CREATE INDEX IF NOT EXISTS idx_stocktake_order_tenant_owner_order_no ON stocktake_order(tenant_id, owner_code, order_no);
|
|
""");
|
|
}
|
|
|
|
private void SeedDemoData()
|
|
{
|
|
Execute.Sql("""
|
|
INSERT INTO warehouse (id, tenant_id, owner_code, code, name, attrs, create_time, create_by, is_deleted)
|
|
VALUES (900000000000000001, 'default', 'default', 'WH-DEMO', 'Demo Warehouse', '{}'::jsonb, NOW(), 'system', FALSE)
|
|
ON CONFLICT (id) DO NOTHING;
|
|
|
|
INSERT INTO zone (id, tenant_id, owner_code, warehouse_code, code, name, attrs, create_time, create_by, is_deleted)
|
|
VALUES (900000000000000002, 'default', 'default', 'WH-DEMO', 'A', 'Default Zone', '{}'::jsonb, NOW(), 'system', FALSE)
|
|
ON CONFLICT (id) DO NOTHING;
|
|
|
|
INSERT INTO location (id, tenant_id, owner_code, warehouse_code, zone_code, code, name, location_status, is_outer, attrs, create_time, create_by, is_deleted)
|
|
VALUES (900000000000000003, 'default', 'default', 'WH-DEMO', 'A', 'A-01-01', 'Default Location', 1, FALSE, '{}'::jsonb, NOW(), 'system', FALSE)
|
|
ON CONFLICT (id) DO NOTHING;
|
|
|
|
INSERT INTO sys_template (id, tenant_id, owner_code, biz_type, biz_name, config_json, create_time, create_by, is_deleted)
|
|
VALUES
|
|
(900000000000000101, 'default', 'default', 'battery_inventory', 'Battery Inventory', '[{"field":"batchNo","label":"Batch No","type":"text","required":true,"queryable":true},{"field":"voltage","label":"Voltage","type":"number","queryMode":"eq"},{"field":"capacity","label":"Capacity","type":"number","queryMode":"eq"}]'::jsonb, NOW(), 'system', FALSE),
|
|
(900000000000000102, 'default', 'default', 'steel_inventory', 'Steel Inventory', '[{"field":"batchNo","label":"Batch No","type":"text","required":true,"queryable":true},{"field":"thickness","label":"Thickness","type":"number","queryMode":"eq"},{"field":"grade","label":"Grade","type":"text","queryable":true}]'::jsonb, NOW(), 'system', FALSE),
|
|
(900000000000000103, 'default', 'default', 'food_inventory', 'Food Inventory', '[{"field":"batchNo","label":"Batch No","type":"text","required":true,"queryable":true},{"field":"productionDate","label":"Production Date","type":"date","queryable":true},{"field":"expiryDate","label":"Expiry Date","type":"date","queryable":true}]'::jsonb, NOW(), 'system', FALSE),
|
|
(900000000000000104, 'default', 'default', 'apparel_inventory', 'Apparel Inventory', '[{"field":"sku","label":"SKU","type":"text","required":true,"queryable":true},{"field":"color","label":"Color","type":"text","queryable":true},{"field":"size","label":"Size","type":"text","queryable":true}]'::jsonb, NOW(), 'system', FALSE)
|
|
ON CONFLICT (id) DO NOTHING;
|
|
""");
|
|
}
|
|
}
|