IntelligentRailwayCosting/Doc/Docker部署/02使用sqlserver数据库/init_sqlserver.sql

IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'iwb_railway_costing_v1')
BEGIN
    CREATE DATABASE iwb_railway_costing_v1
END
GO

USE iwb_railway_costing_v1
GO

USE iwb_railway_costing_v1
GO



/*-- 删除项目任务表
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[project_task]') AND type in (N'U'))
BEGIN
    DROP TABLE [dbo].[project_task]
END
GO*/

-- 创建项目任务表
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[project_task]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[project_task] (
    [id] INT IDENTITY(1,1) PRIMARY KEY,
    [task_name] NVARCHAR(255) NOT NULL,
    [task_sort] INT DEFAULT 0,
    [task_desc] NVARCHAR(1000) NULL,
    [is_cover]  INT DEFAULT 0,
    [project_id] NVARCHAR(50) NOT NULL,
    [budget_id] INT DEFAULT 0,
    [item_id] INT NOT NULL,
    [item_code] NVARCHAR(255) NOT NULL,
    [file_path] NVARCHAR(MAX) NULL,
    [process_status] INT NOT NULL DEFAULT 0,
    [process_time] DATETIME NULL,
    [process_error] NVARCHAR(4000) NULL,
    [send_status] INT NOT NULL DEFAULT 0,
    [send_time] DATETIME NULL,
    [send_error] NVARCHAR(4000) NULL,
    [is_del] INT NOT NULL DEFAULT 0,
    [deleted_by] NVARCHAR(50) NULL,
    [deleted_at] DATETIME NULL,
    [created_by] NVARCHAR(50) NULL,
    [created_at] DATETIME NOT NULL DEFAULT GETDATE(),
    [updated_by] NVARCHAR(50) NULL,
    [updated_at] DATETIME NOT NULL DEFAULT GETDATE()
)

CREATE INDEX [idx_project_id] ON [dbo].[project_task] ([project_id])
CREATE INDEX [idx_budget_id] ON [dbo].[project_task] ([budget_id])
CREATE INDEX [idx_item_id] ON [dbo].[project_task] ([item_id])
CREATE INDEX [idx_item_code] ON [dbo].[project_task] ([item_code])
CREATE INDEX [idx_created_at] ON [dbo].[project_task] ([created_at])
END
GO

/* -- 删除项目定额表
   IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[project_quota]') AND type in (N'U'))
   BEGIN
       DROP TABLE [dbo].[project_quota]
   END
   GO
*/
-- 创建项目定额表
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[project_quota]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[project_quota] (
    [id] INT IDENTITY(1,1) PRIMARY KEY,
    [task_id] INT NOT NULL,
    [project_id] NVARCHAR(50) NOT NULL,
    [budget_id] INT NOT NULL,
    [budget_code] NVARCHAR(50) NULL,
    [item_id] INT NOT NULL,
    [item_code] NVARCHAR(255) NOT NULL,
    [quota_id] INT NULL DEFAULT 0,
    [quota_code] NVARCHAR(50) NULL,
    [entry_name] NVARCHAR(255) NULL,
    [units] NVARCHAR(20) NULL,
    [amount] FLOAT NULL,
    [ex_file] NVARCHAR(4000) NULL,
    [ex_cell] NVARCHAR(50) NULL,
    [ex_row] NVARCHAR(4000) NULL,
    [ex_unit] NVARCHAR(50) NULL,
    [ex_amount] FLOAT NULL,
    [send_status] INT NOT NULL DEFAULT 0,
    [send_time] DATETIME NULL,
    [send_error] NVARCHAR(4000) NULL,
    [is_del] INT NOT NULL DEFAULT 0,
    [deleted_by] NVARCHAR(50) NULL,
    [deleted_at] DATETIME NULL,
    [created_by] NVARCHAR(50) NULL,
    [created_at] DATETIME NOT NULL DEFAULT GETDATE(),
    [updated_by] NVARCHAR(50) NULL,
    [updated_at] DATETIME NOT NULL DEFAULT GETDATE()
)

CREATE INDEX [idx_project_id] ON [dbo].[project_quota] ([project_id])
CREATE INDEX [idx_budget_id] ON [dbo].[project_quota] ([budget_id])
CREATE INDEX [idx_item_id] ON [dbo].[project_quota] ([item_id])
CREATE INDEX [idx_item_code] ON [dbo].[project_quota] ([item_code])
CREATE INDEX [idx_created_at] ON [dbo].[project_quota] ([created_at])
END
GO


/*-- 删除日志表
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sys_log]') AND type in (N'U'))
BEGIN
    DROP TABLE [dbo].[sys_log]
END
GO*/

-- 创建日志表
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sys_log]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[sys_log] (
    [id] INT IDENTITY(1,1) PRIMARY KEY,
    [username] NVARCHAR(255) NOT NULL,
    [operation_type] NVARCHAR(50) NOT NULL,
    [operation_desc] NVARCHAR(1000) NULL,
    [operation_result] INT NULL,
    [operation_module] NVARCHAR(100) NULL,
    [operation_data] NVARCHAR(MAX) NULL,
    [data_changes] NVARCHAR(MAX) NULL,
    [operation_ip] NVARCHAR(50) NULL,
    [created_at] DATETIME NOT NULL DEFAULT GETDATE()
)

CREATE INDEX [idx_username] ON [dbo].[sys_log] ([username])
CREATE INDEX [idx_created_at] ON [dbo].[sys_log] ([created_at])
END
GO