287 lines
11 KiB
C#
287 lines
11 KiB
C#
using FluentMigrator;
|
|
using FluentMigrator.Builders.Create.Table;
|
|
|
|
namespace WmsLite.Api.Database.Migrations;
|
|
|
|
[Migration(11, "Area table; zone/location/inventory area_code; order lines use area_code; transfer from/to area; tx snapshot")]
|
|
public class AreaWarehouseHierarchy0011 : Migration
|
|
{
|
|
public override void Up()
|
|
{
|
|
CreateAreaTable();
|
|
SeedAreasAndBackfillZones();
|
|
AlterLocationInventory();
|
|
AlterOrderLines();
|
|
AlterInventoryTransaction();
|
|
RefreshIndexes();
|
|
}
|
|
|
|
private void CreateAreaTable()
|
|
{
|
|
if (Schema.Table("area").Exists())
|
|
{
|
|
return;
|
|
}
|
|
|
|
Create.Table("area")
|
|
.WithBaseColumns()
|
|
.WithColumn("warehouse_code").AsString(64).NotNullable()
|
|
.WithColumn("code").AsString(64).NotNullable()
|
|
.WithColumn("name").AsString(256).NotNullable()
|
|
.WithColumn("attrs").AsCustom("jsonb").Nullable();
|
|
}
|
|
|
|
private void SeedAreasAndBackfillZones()
|
|
{
|
|
if (!Schema.Table("zone").Exists())
|
|
{
|
|
return;
|
|
}
|
|
|
|
Execute.Sql("""
|
|
ALTER TABLE zone ADD COLUMN IF NOT EXISTS area_code varchar(64);
|
|
UPDATE zone SET area_code = 'DEFAULT' WHERE area_code IS NULL;
|
|
ALTER TABLE zone ALTER COLUMN area_code SET NOT NULL;
|
|
|
|
WITH d AS (
|
|
SELECT DISTINCT tenant_id, owner_code, warehouse_code
|
|
FROM zone
|
|
WHERE NOT is_deleted
|
|
UNION
|
|
SELECT DISTINCT tenant_id, owner_code, warehouse_code
|
|
FROM location
|
|
WHERE NOT is_deleted
|
|
UNION
|
|
SELECT DISTINCT tenant_id, owner_code, warehouse_code
|
|
FROM inventory
|
|
WHERE NOT is_deleted
|
|
), m AS (
|
|
SELECT GREATEST(
|
|
COALESCE((SELECT MAX(id) FROM area), 0),
|
|
COALESCE((SELECT MAX(id) FROM zone), 0),
|
|
COALESCE((SELECT MAX(id) FROM location), 0)
|
|
)::bigint AS mx
|
|
)
|
|
INSERT INTO area (id, tenant_id, owner_code, warehouse_code, code, name, attrs, create_time, update_time, is_deleted)
|
|
SELECT
|
|
m.mx + ROW_NUMBER() OVER (ORDER BY d.tenant_id, d.owner_code, d.warehouse_code)::bigint,
|
|
d.tenant_id,
|
|
d.owner_code,
|
|
d.warehouse_code,
|
|
'DEFAULT',
|
|
'默认库区',
|
|
'{}'::jsonb,
|
|
NOW(),
|
|
NOW(),
|
|
FALSE
|
|
FROM d
|
|
CROSS JOIN m
|
|
WHERE NOT EXISTS (
|
|
SELECT 1 FROM area a
|
|
WHERE NOT a.is_deleted AND a.tenant_id = d.tenant_id AND a.owner_code = d.owner_code
|
|
AND a.warehouse_code = d.warehouse_code AND a.code = 'DEFAULT');
|
|
|
|
DO $$ BEGIN
|
|
ALTER TABLE zone ADD CONSTRAINT uq_zone_wh_area_code UNIQUE (tenant_id, owner_code, warehouse_code, area_code, code);
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
""");
|
|
}
|
|
|
|
private void AlterLocationInventory()
|
|
{
|
|
if (!Schema.Table("location").Exists())
|
|
{
|
|
return;
|
|
}
|
|
|
|
Execute.Sql("""
|
|
ALTER TABLE location ADD COLUMN IF NOT EXISTS area_code varchar(64);
|
|
UPDATE location l SET area_code = z.area_code
|
|
FROM zone z
|
|
WHERE NOT l.is_deleted AND NOT z.is_deleted
|
|
AND l.tenant_id = z.tenant_id AND l.owner_code = z.owner_code
|
|
AND l.warehouse_code = z.warehouse_code AND l.zone_code = z.code
|
|
AND l.area_code IS NULL;
|
|
UPDATE location SET area_code = 'DEFAULT' WHERE area_code IS NULL;
|
|
ALTER TABLE location ALTER COLUMN area_code SET NOT NULL;
|
|
""");
|
|
|
|
if (!Schema.Table("inventory").Exists())
|
|
{
|
|
return;
|
|
}
|
|
|
|
Execute.Sql("""
|
|
ALTER TABLE inventory ADD COLUMN IF NOT EXISTS area_code varchar(64);
|
|
UPDATE inventory inv SET area_code = l.area_code
|
|
FROM location l
|
|
WHERE NOT inv.is_deleted AND NOT l.is_deleted
|
|
AND inv.tenant_id = l.tenant_id AND inv.owner_code = l.owner_code
|
|
AND inv.warehouse_code = l.warehouse_code AND inv.zone_code = l.zone_code AND inv.location_code = l.code
|
|
AND inv.area_code IS NULL;
|
|
UPDATE inventory SET area_code = 'DEFAULT' WHERE area_code IS NULL;
|
|
ALTER TABLE inventory ALTER COLUMN area_code SET NOT NULL;
|
|
""");
|
|
}
|
|
|
|
private void AlterOrderLines()
|
|
{
|
|
foreach (var table in new[] { "inbound_order_line", "outbound_order_line", "stocktake_order_line" })
|
|
{
|
|
if (!Schema.Table(table).Exists())
|
|
{
|
|
continue;
|
|
}
|
|
|
|
Execute.Sql($@"
|
|
ALTER TABLE {table} ADD COLUMN IF NOT EXISTS area_code varchar(64);
|
|
UPDATE {table} SET area_code = 'DEFAULT' WHERE area_code IS NULL;
|
|
ALTER TABLE {table} DROP COLUMN IF EXISTS zone_code CASCADE;
|
|
ALTER TABLE {table} ALTER COLUMN area_code SET NOT NULL;
|
|
");
|
|
}
|
|
|
|
if (!Schema.Table("transfer_order_line").Exists())
|
|
{
|
|
return;
|
|
}
|
|
|
|
Execute.Sql("""
|
|
ALTER TABLE transfer_order_line ADD COLUMN IF NOT EXISTS from_area_code varchar(64);
|
|
ALTER TABLE transfer_order_line ADD COLUMN IF NOT EXISTS to_area_code varchar(64);
|
|
UPDATE transfer_order_line SET from_area_code = 'DEFAULT', to_area_code = 'DEFAULT' WHERE from_area_code IS NULL OR to_area_code IS NULL;
|
|
ALTER TABLE transfer_order_line DROP COLUMN IF EXISTS from_zone_code CASCADE;
|
|
ALTER TABLE transfer_order_line DROP COLUMN IF EXISTS to_zone_code CASCADE;
|
|
ALTER TABLE transfer_order_line ALTER COLUMN from_area_code SET NOT NULL;
|
|
ALTER TABLE transfer_order_line ALTER COLUMN to_area_code SET NOT NULL;
|
|
""");
|
|
}
|
|
|
|
private void AlterInventoryTransaction()
|
|
{
|
|
if (!Schema.Table("inventory_transaction").Exists())
|
|
{
|
|
return;
|
|
}
|
|
|
|
Execute.Sql("""
|
|
ALTER TABLE inventory_transaction ADD COLUMN IF NOT EXISTS warehouse_code varchar(64);
|
|
ALTER TABLE inventory_transaction ADD COLUMN IF NOT EXISTS area_code varchar(64);
|
|
ALTER TABLE inventory_transaction ADD COLUMN IF NOT EXISTS zone_code varchar(64);
|
|
|
|
UPDATE inventory_transaction t SET
|
|
warehouse_code = i.warehouse_code,
|
|
area_code = i.area_code,
|
|
zone_code = i.zone_code
|
|
FROM inventory i
|
|
WHERE t.inventory_id = i.id AND NOT i.is_deleted;
|
|
|
|
UPDATE inventory_transaction SET
|
|
warehouse_code = 'DEFAULT',
|
|
area_code = 'DEFAULT',
|
|
zone_code = 'DEFAULT'
|
|
WHERE warehouse_code IS NULL OR area_code IS NULL OR zone_code IS NULL;
|
|
|
|
ALTER TABLE inventory_transaction ALTER COLUMN warehouse_code SET NOT NULL;
|
|
ALTER TABLE inventory_transaction ALTER COLUMN area_code SET NOT NULL;
|
|
ALTER TABLE inventory_transaction ALTER COLUMN zone_code SET NOT NULL;
|
|
""");
|
|
}
|
|
|
|
private void RefreshIndexes()
|
|
{
|
|
Execute.Sql("""
|
|
DROP INDEX IF EXISTS idx_zone_tenant_owner_warehouse_code;
|
|
CREATE INDEX IF NOT EXISTS idx_zone_tenant_owner_wh_area_code ON zone(tenant_id, owner_code, warehouse_code, area_code, code);
|
|
|
|
DROP INDEX IF EXISTS idx_location_tenant_owner_wzl_code;
|
|
CREATE INDEX IF NOT EXISTS idx_location_tenant_owner_wazl ON location(tenant_id, owner_code, warehouse_code, area_code, zone_code, code);
|
|
|
|
DROP INDEX IF EXISTS idx_inventory_tenant_owner_location;
|
|
CREATE INDEX IF NOT EXISTS idx_inventory_tenant_owner_wazl ON inventory(tenant_id, owner_code, warehouse_code, area_code, zone_code, location_code);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_area_tenant_owner_wh_code ON area(tenant_id, owner_code, warehouse_code, code);
|
|
""");
|
|
}
|
|
|
|
public override void Down()
|
|
{
|
|
Execute.Sql("""
|
|
DROP INDEX IF EXISTS idx_area_tenant_owner_wh_code;
|
|
|
|
DROP INDEX IF EXISTS idx_inventory_tenant_owner_wazl;
|
|
CREATE INDEX IF NOT EXISTS idx_inventory_tenant_owner_location ON inventory(tenant_id, owner_code, warehouse_code, zone_code, location_code);
|
|
|
|
DROP INDEX IF EXISTS idx_location_tenant_owner_wazl;
|
|
CREATE INDEX IF NOT EXISTS idx_location_tenant_owner_wzl_code ON location(tenant_id, owner_code, warehouse_code, zone_code, code);
|
|
|
|
DROP INDEX IF EXISTS idx_zone_tenant_owner_wh_area_code;
|
|
CREATE INDEX IF NOT EXISTS idx_zone_tenant_owner_warehouse_code ON zone(tenant_id, owner_code, warehouse_code, code);
|
|
""");
|
|
|
|
if (Schema.Table("inventory_transaction").Exists())
|
|
{
|
|
if (Schema.Table("inventory_transaction").Column("zone_code").Exists())
|
|
{
|
|
Delete.Column("zone_code").FromTable("inventory_transaction");
|
|
}
|
|
|
|
if (Schema.Table("inventory_transaction").Column("area_code").Exists())
|
|
{
|
|
Delete.Column("area_code").FromTable("inventory_transaction");
|
|
}
|
|
|
|
if (Schema.Table("inventory_transaction").Column("warehouse_code").Exists())
|
|
{
|
|
Delete.Column("warehouse_code").FromTable("inventory_transaction");
|
|
}
|
|
}
|
|
|
|
if (Schema.Table("transfer_order_line").Exists())
|
|
{
|
|
Execute.Sql("""
|
|
ALTER TABLE transfer_order_line ADD COLUMN IF NOT EXISTS from_zone_code varchar(64) NOT NULL DEFAULT 'DEFAULT';
|
|
ALTER TABLE transfer_order_line ADD COLUMN IF NOT EXISTS to_zone_code varchar(64) NOT NULL DEFAULT 'DEFAULT';
|
|
ALTER TABLE transfer_order_line DROP COLUMN IF EXISTS from_area_code CASCADE;
|
|
ALTER TABLE transfer_order_line DROP COLUMN IF EXISTS to_area_code CASCADE;
|
|
""");
|
|
}
|
|
|
|
foreach (var table in new[] { "inbound_order_line", "outbound_order_line", "stocktake_order_line" })
|
|
{
|
|
if (Schema.Table(table).Exists())
|
|
{
|
|
Execute.Sql($@"
|
|
ALTER TABLE {table} ADD COLUMN IF NOT EXISTS zone_code varchar(64) NOT NULL DEFAULT 'DEFAULT';
|
|
ALTER TABLE {table} DROP COLUMN IF EXISTS area_code CASCADE;
|
|
");
|
|
}
|
|
}
|
|
|
|
if (Schema.Table("inventory").Exists() && Schema.Table("inventory").Column("area_code").Exists())
|
|
{
|
|
Delete.Column("area_code").FromTable("inventory");
|
|
}
|
|
|
|
if (Schema.Table("location").Exists() && Schema.Table("location").Column("area_code").Exists())
|
|
{
|
|
Delete.Column("area_code").FromTable("location");
|
|
}
|
|
|
|
if (Schema.Table("zone").Exists() && Schema.Table("zone").Column("area_code").Exists())
|
|
{
|
|
Execute.Sql("""
|
|
ALTER TABLE zone DROP CONSTRAINT IF EXISTS uq_zone_wh_area_code;
|
|
ALTER TABLE zone DROP COLUMN IF EXISTS area_code;
|
|
""");
|
|
}
|
|
|
|
if (Schema.Table("area").Exists())
|
|
{
|
|
Delete.Table("area");
|
|
}
|
|
}
|
|
}
|