--巡更系統表
IF OBJECT_ID('dbo.XG_Person') IS NULL
CREATE TABLE dbo.XG_Person (
       Person_ID            int NOT NULL,
       Person_No            varchar(20) NOT NULL,
       Person_Name          varchar(20) NOT NULL,
       Sex                  varchar(10) NULL,
       Card_No              varchar(12) NULL,
       Tel                  varchar(20) NULL,
       Remark               varchar(100) NULL,
       Dept_ID              int NULL,
       XGClass              tinyint,
       Station_ID           INT NOT NULL DEFAULT 0,
       Route_ID             INT NOT NULL DEFAULT 0,
       PRIMARY KEY (Person_ID ASC)
)
go

IF OBJECT_ID('dbo.XG_MOC') IS NULL
CREATE TABLE dbo.XG_MOC (
       Moc_No               varchar(6) NOT NULL,
       Moc_Name             varchar(20) NOT NULL,
       Moc_Spec             varchar(20) NULL,
       Station_ID           INT NOT NULL DEFAULT 0,
       Local_No             VARCHAR(6),
       Person_ID            INT DEFAULT 0,
       Remark               varchar(100) NULL,       
       PRIMARY KEY (Moc_No ASC)
)
go

IF OBJECT_ID('dbo.XG_Event') IS NULL
CREATE TABLE dbo.XG_Event (
       Event_No             varchar(10) NOT NULL,
       Event_Name           varchar(20) NOT NULL,
       Remark               varchar(100) NULL,
       PRIMARY KEY (Event_No ASC)
)
go

IF OBJECT_ID('dbo.XG_Site') IS NULL
CREATE TABLE dbo.XG_Site (
       Site_ID              int NOT NULL,
       Site_No              varchar(10) NOT NULL,
       Site_Name            varchar(20) NOT NULL,
       Card_No              varchar(12) NOT NULL,
       Station_ID           INT NOT NULL DEFAULT 0,
       Remark               varchar(100) NULL,
       PRIMARY KEY (Site_ID ASC)
)
go

IF OBJECT_ID('dbo.XG_Route') IS NULL
CREATE TABLE dbo.XG_Route (
       Route_ID             int NOT NULL,
       Route_No             varchar(10) NOT NULL,
       Route_Name           varchar(20) NOT NULL,
       Bgn_Time             varchar(5) NOT NULL,
       End_Time             varchar(5) NOT NULL,
       Total_Time           int NULL,
       Bgn_SiteID           int NOT NULL,
       End_SiteID           int NOT NULL,
       Station_ID           INT NOT NULL DEFAULT 0,
       Local_No             VARCHAR(6),
       Remark               varchar(100) NULL,
       ImgFile              varchar(100) NULL,
       PRIMARY KEY (Route_ID ASC)
)
go

IF OBJECT_ID('dbo.XG_RouteSite') IS NULL
CREATE TABLE dbo.XG_RouteSite (
       ID_KEY               int IDENTITY,
       Route_ID             int NOT NULL,
       Site_ID              int NOT NULL,
       After_Days           int NOT NULL DEFAULT 0,
       Plan_Time            varchar(5) NOT NULL,
       In_Range             smallint NOT NULL,
       Out_Range            smallint NOT NULL,
       SiteX                int NULL DEFAULT 0,
       SiteY                int NULL DEFAULT 0,
       PRIMARY KEY (ID_KEY ASC)
)
go

IF OBJECT_ID('dbo.XG_SupplyEvent') IS NULL
CREATE TABLE dbo.XG_SupplyEvent (
       ID_KEY               int IDENTITY,
       Person_ID            int NOT NULL,
       Route_ID             int NULL,
       Site_ID              int NOT NULL,
       Event_No             varchar(10) NULL,
       Deal_TimeLen         int NULL,
       Supply_Date          datetime NULL,
       After_Days           int NULL DEFAULT 0,
       Reason               varchar(100) NULL,
       Audit_Man            varchar(20) NULL,
       PRIMARY KEY (ID_KEY ASC)
)
go

IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_XG_SupplyEvent')
CREATE INDEX IX_XG_SupplyEvent ON dbo.XG_SupplyEvent
(
       Supply_Date                   ASC
)
GO

IF OBJECT_ID('dbo.XG_SupplyPatrol') IS NULL
CREATE TABLE dbo.XG_SupplyPatrol (
       ID_KEY               int IDENTITY,
       Person_ID            int NOT NULL,
       Route_ID             int NOT NULL,
       Site_ID              int NOT NULL,
       Supply_Date          varchar(10) NOT NULL,
       Supply_Time          varchar(5) NOT NULL,
       Event_No             varchar(10) NULL,
       After_Days           int NULL DEFAULT 0,
       Reason               varchar(100) NULL,
       Audit_Man            varchar(20) NULL,
       PRIMARY KEY (ID_KEY ASC)
)
go

IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_XG_SupplyPatrol')
CREATE INDEX IX_XG_SupplyPatrol ON dbo.XG_SupplyPatrol
(
       Supply_Date                   ASC,
       Supply_Time                   ASC
)
GO

IF OBJECT_ID('dbo.XG_Download') IS NULL
CREATE TABLE dbo.XG_Download (
       ID_KEY               int IDENTITY,
       Moc_No               varchar(6) NULL,
       Person_No            varchar(20) NOT NULL,
       Person_ID            int NOT NULL,
       Route_No             varchar(10) NULL,
       Card_No              varchar(12) NULL,
       Site_ID              int NOT NULL,
       Patrol_Date          varchar(10) NOT NULL,
       Patrol_Time          varchar(5) NOT NULL,
       Event_No             varchar(10) NULL,
       PRIMARY KEY (ID_KEY ASC)
)
go

IF OBJECT_ID('dbo.XG_Scheme') IS NULL
CREATE TABLE dbo.XG_Scheme (
       Scheme_ID            int NOT NULL,
       Scheme_Name          varchar(20) NOT NULL,
       Route_ID             int NOT NULL,
       Shift_Days           smallint NOT NULL,
       iIndex               int NOT NULL,
       PRIMARY KEY (Scheme_ID ASC, Route_ID ASC)
)
go

IF OBJECT_ID('dbo.XG_Work') IS NULL
CREATE TABLE dbo.XG_Work (
       ID_KEY               int IDENTITY,
       Person_ID            int NOT NULL,
       Work_Date            varchar(10) NOT NULL,
       Route_ID             int NOT NULL,
       PRIMARY KEY (ID_KEY ASC)
)
go

IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_XG_Work')
CREATE INDEX IX_XG_Work ON dbo.XG_Work
(
       Work_Date                   ASC
)
GO

IF OBJECT_ID('dbo.XG_AdjustWork') IS NULL
CREATE TABLE dbo.XG_AdjustWork (
       ID_KEY               int IDENTITY,
       Person_ID            int NOT NULL,
       Adjust_Date          varchar(10) NOT NULL,
       Route_ID             int NOT NULL,
       Reason               varchar(100) NULL,
       Audit_Man            varchar(20) NULL,
       PRIMARY KEY (ID_KEY ASC)
)
go

IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_XG_AdjustWork')
CREATE INDEX IX_XG_AdjustWork ON dbo.XG_AdjustWork
(
       Adjust_Date                   ASC
)
GO

IF OBJECT_ID('dbo.XG_RouteDaily') IS NULL
CREATE TABLE dbo.XG_RouteDaily (
       ID_KEY               int IDENTITY,
       Route_ID             varchar(20) NOT NULL,
       Patrol_Date          varchar(10) NOT NULL,
       Patrol_Week          int NULL,
       Site_ID              int NULL,
       Patrol_State         tinyint NOT NULL,
       After_Days           int NULL DEFAULT 0,
       Plan_Time            varchar(5) NULL,
       Fact_Time            varchar(5) NULL,
       Event_No             varchar(10) NULL,
       PRIMARY KEY (ID_KEY ASC)
)
go

IF OBJECT_ID('dbo.XG_PsnDaily') IS NULL
CREATE TABLE dbo.XG_PsnDaily (
       ID_KEY               int IDENTITY,
       Person_ID            int NOT NULL,
       Patrol_Date          varchar(10) NOT NULL,
       Patrol_Week          int NULL,
       Route_ID             int NOT NULL,
       Site_ID              int NULL,
       Patrol_State         tinyint NOT NULL,
       After_Days           int NULL DEFAULT 0,
       Plan_Time            varchar(5) NULL,
       Fact_Time            varchar(5) NULL,
       Event_No             varchar(10) NULL,
       PRIMARY KEY (ID_KEY ASC)
)
go

IF OBJECT_ID('dbo.XG_RouteSum') IS NULL
CREATE TABLE dbo.XG_RouteSum (
       Route_No             varchar(10) NOT NULL,
       Route_Name           varchar(20) NOT NULL,
       Site_Name            varchar(20) NULL,
       OnTime               int NULL,
       BfTime               int NULL,
       AfTime               int NULL,
       LkTime               int NULL,
       NtTime               int NULL
)
go

IF OBJECT_ID('dbo.XG_PsnSum') IS NULL
CREATE TABLE dbo.XG_PsnSum (
       Person_No            varchar(20) NOT NULL,
       Person_Name          varchar(20) NOT NULL,
       OnTime               int NULL,
       BfTime               int NULL,
       AfTime               int NULL,
       LkTime               int NULL,
       NtTime               int NULL
)
go

--核心平台系統表
IF OBJECT_ID('dbo.ST_SubSystem') IS NULL
CREATE TABLE dbo.ST_SubSystem (
       Remark               varchar(50) NULL,
       System_ExeName       varchar(20) NULL,
       System_Title         varchar(20) NULL,
       System_Name          varchar(20) NOT NULL,
       System_No            varchar(10) NOT NULL,
       Index_No             int NULL,
       DBCreateDate         datetime NULL,
       DBVersion            varchar(10) NULL,
       DBUpdateinfo         varchar(50) NULL,
       IsCreateTable        bit NULL DEFAULT 0,
       IsInitTable          bit NULL DEFAULT 0,
       Update_State1        tinyint NULL DEFAULT 0,
       Update_State2        tinyint NULL DEFAULT 0,
       Update_State3        tinyint NULL DEFAULT 0,
       PRIMARY KEY (System_No ASC)
)
go

IF OBJECT_ID('dbo.ST_Identity') IS NULL
CREATE TABLE dbo.ST_Identity (
       Table_Name           varchar(40) NOT NULL,
       Current_ID           int NOT NULL,
       PRIMARY KEY (Table_Name ASC)
)
go

IF OBJECT_ID('dbo.ST_RoleInfo') IS NULL
CREATE TABLE dbo.ST_RoleInfo (
       Remark               varchar(50) NULL,
       Role_No              varchar(20) NOT NULL,
       Role_Name            varchar(20) NOT NULL,
       PRIMARY KEY (Role_No ASC)
)
go

IF OBJECT_ID('dbo.ST_UserInfo') IS NULL
CREATE TABLE dbo.ST_UserInfo (
       User_Name            varchar(20) NOT NULL,
       User_No              varchar(20) NOT NULL,
       Remark               varchar(50) NULL,
       User_Moc             varchar(200) NULL,
       Card_No              varchar(12) NULL,
       User_Type            tinyint NULL DEFAULT 0,
       User_Pwd             varchar(20) NULL,
       Role_No              varchar(20) NULL,
       Is_Del               bit NULL DEFAULT 0,
       Is_Using             bit NULL,
       Login_Time           datetime NULL,
       Computer_Name        varchar(255) NULL,
       Using_Model          varchar(100) NULL,
       Using_Form           varchar(40) NULL,
       Station_ID           INT NOT NULL DEFAULT 0,
       PRIMARY KEY (User_No ASC), 
       FOREIGN KEY (Role_No)
                             REFERENCES ST_RoleInfo  (Role_No)
                             ON DELETE NO ACTION
                             ON UPDATE NO ACTION
)
go

IF OBJECT_ID('dbo.ST_SysLog') IS NULL
CREATE TABLE dbo.ST_SysLog (
       System_No            varchar(10) NOT NULL,
       Opt_log              varchar(50) NULL,
       Opt_Computer         varchar(20) NULL,
       Opt_Time             datetime NOT NULL DEFAULT GETDATE(),
       User_Name            varchar(20) NOT NULL,
       ID_KEY               int IDENTITY,
       PRIMARY KEY (ID_KEY ASC)
)
go

IF OBJECT_ID('dbo.ST_MenuInfo') IS NULL
CREATE TABLE dbo.ST_MenuInfo (
       Menu_ID              int NOT NULL,
       Menu_No              varchar(20) NOT NULL,
       Menu_Name            varchar(20) NOT NULL,
       System_No            varchar(20) NOT NULL,
       Menu_Hint            varchar(20) NULL,
       Para_ID              int NULL DEFAULT -1,
       Call_Mode            tinyint NULL DEFAULT 1,
       Form_Name            varchar(50) NULL,
       ShortKey             varchar(40) NULL,
       Menu_Index           tinyint NULL DEFAULT 1,
       Image_Index          smallint NULL DEFAULT -1,
       Separator            tinyint NULL DEFAULT 0,
       InToolBar            bit NULL DEFAULT 0,
       Remark               varchar(50) NULL,
       PRIMARY KEY (Menu_ID ASC)
)
go

IF OBJECT_ID('dbo.ST_RightInfo') IS NULL
CREATE TABLE dbo.ST_RightInfo (
       Remark               varchar(50) NULL,
       Menu_ID              int NOT NULL,
       Right_Values         varchar(40) NULL,
       Form_Name            varchar(50) NOT NULL,
       PRIMARY KEY (Menu_ID ASC), 
       FOREIGN KEY (Menu_ID)
                             REFERENCES ST_MenuInfo  (Menu_ID)
                             ON DELETE NO ACTION
                             ON UPDATE NO ACTION
)
go

IF OBJECT_ID('dbo.ST_SysParam') IS NULL
CREATE TABLE dbo.ST_SysParam (
       ID_KEY               int IDENTITY,
       Param_Code           varchar(20) NOT NULL,
       Param_Name           varchar(100) NOT NULL,
       Param_Value          varchar(100) NOT NULL,
       PRIMARY KEY (ID_KEY ASC)
)
go

IF OBJECT_ID('dbo.ST_RightValue') IS NULL
CREATE TABLE dbo.ST_RightValue (
       Remark               varchar(50) NULL,
       Right_ID             char(1) NOT NULL,
       Right_Name           varchar(20) NOT NULL,
       Form_Name            varchar(50) NOT NULL,
       PRIMARY KEY (Right_ID ASC, Form_Name ASC)
)
go

IF OBJECT_ID('dbo.ST_RoleRight') IS NULL
CREATE TABLE dbo.ST_RoleRight (
       ID_KEY               int IDENTITY,
       Menu_ID              int NOT NULL,
       Right_Values         varchar(40) NULL,
       Role_No              varchar(20) NOT NULL,
       PRIMARY KEY (ID_KEY ASC), 
       FOREIGN KEY (Menu_ID)
                             REFERENCES ST_MenuInfo  (Menu_ID)
                             ON DELETE NO ACTION
                             ON UPDATE NO ACTION, 
       FOREIGN KEY (Role_No)
                             REFERENCES ST_RoleInfo  (Role_No)
                             ON DELETE NO ACTION
                             ON UPDATE NO ACTION
)
go

IF OBJECT_ID('dbo.XG_Attendance') IS NULL
CREATE TABLE dbo.XG_Attendance (
       ID_KEY               int IDENTITY,
       Moc_No               varchar(6) NULL,
       Person_No            varchar(20) NOT NULL,
       Person_ID            int NOT NULL,
       Route_No             varchar(10) NULL,
       Card_No              varchar(12) NULL,
       Site_ID              int NOT NULL,
       Patrol_Date          varchar(10) NOT NULL,
       Patrol_Time          varchar(5) NOT NULL,
       Event_No             varchar(10) NULL,
       RecordTag            varchar(5) NULL,
       PRIMARY KEY (ID_KEY ASC)
)
go

IF OBJECT_ID('dbo.XG_PatrolStation') IS NULL
   CREATE TABLE dbo.XG_PatrolStation (
       Station_ID            int NOT NULL,
       Station_No            varchar(20) NOT NULL,
       Station_Code          varchar(20) NOT NULL,
       Station_Name          varchar(20) NOT NULL,
       Station_User          varchar(10) NULL,
       Addr                  varchar(12) NULL,
       Tel                   varchar(20) NULL,
       Remark                varchar(100) NULL,
       Is_Station            bit NOT NULL default 0,
       PRIMARY KEY (Station_ID ASC)
   )
GO
