--售飯與靈活消費的公用表結構
IF OBJECT_ID('dbo.XF_ConsumeTemp') IS NULL
CREATE TABLE dbo.XF_ConsumeTemp (
       ID_KEY               bigint IDENTITY,
       Person_ID            int NOT NULL,
       System_No            varchar(10) NOT NULL DEFAULT 'SF',
       Consume_Mode         int NOT NULL,
       Consume_Time         datetime NOT NULL DEFAULT GETDATE(),
       Consume_Fund         money NOT NULL DEFAULT 0.00,
       Before_Fund          money NOT NULL DEFAULT 0.00,
       Consume_Copy         int NULL DEFAULT 0,
       Consume_TimeLen      float NULL DEFAULT 0,
       Consume_Capacity     float NULL DEFAULT 0,
       Repast_No            varchar(3) NULL,
       Ware_ID              int NULL,
       MOC_No               varchar(6) NOT NULL,
       COM_No               varchar(6) NULL,
       Card_Balance         money NOT NULL DEFAULT 0.00,
       DB_Balance           money NULL DEFAULT 0.00,
       Card_SmallAccount    money NULL DEFAULT 0.00,
       Data_Type            tinyint NOT NULL DEFAULT 0,
       Is_Commited          bit NULL DEFAULT 0,
       Source_ID            bigint NULL,
       Download_Time        datetime NULL,
       PRIMARY KEY (ID_KEY ASC)
)
go

IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_XF_ConsumeTemp')
CREATE INDEX IX_XF_ConsumeTemp ON dbo.XF_ConsumeTemp
(
       Consume_Time                   ASC
)
go

IF OBJECT_ID('dbo.XF_Consume') IS NULL
CREATE TABLE dbo.XF_Consume (
       ID_KEY               bigint IDENTITY,
       Person_ID            int NOT NULL,
       System_No            varchar(10) NOT NULL DEFAULT 'SF',
       Consume_Mode         int NOT NULL,
       Consume_Time         datetime NOT NULL DEFAULT GETDATE(),
       Consume_Fund         money NOT NULL DEFAULT 0.00,
       Before_Fund          money NOT NULL DEFAULT 0.00,
       Consume_Copy         int NULL DEFAULT 0,
       Consume_TimeLen      float NULL DEFAULT 0,
       Consume_Capacity     float NULL DEFAULT 0,
       Repast_No            varchar(3) NULL,
       Ware_ID              int NULL,
       MOC_No               varchar(6) NOT NULL,
       COM_No               varchar(6) NULL,
       Card_Balance         money NOT NULL DEFAULT 0.00,
       DB_Balance           money NULL DEFAULT 0.00,
       Card_SmallAccount    money NULL DEFAULT 0.00,
       Data_Type            tinyint NOT NULL DEFAULT 0,
       Remark               varchar(20) NULL,
       Download_Time        datetime NULL,
       PRIMARY KEY (ID_KEY ASC)
)
go

IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_XF_Consume')
CREATE INDEX IX_XF_Consume ON dbo.XF_Consume
(
       Consume_Time                   ASC
)
go

if not exists(select * from sysindexes where name='IX_XF_Consume_Person_ID')
    CREATE  INDEX [IX_XF_Consume_Person_ID] ON [dbo].[XF_Consume]([Person_ID]) ON [PRIMARY]
go

IF OBJECT_ID('dbo.XF_ConsumeHistory') IS NULL
CREATE TABLE dbo.XF_ConsumeHistory (
       ID_KEY               bigint IDENTITY,
       Person_ID            int NOT NULL,
       System_No            varchar(10) NOT NULL DEFAULT 'SF',
       Consume_Mode         int NOT NULL,
       Consume_Time         datetime NOT NULL DEFAULT GETDATE(),
       Consume_Fund         money NOT NULL DEFAULT 0.00,
       Consume_Copy         int NULL DEFAULT 0,
       Consume_TimeLen      float NULL DEFAULT 0,
       Consume_Capacity     float NULL DEFAULT 0,
       Repast_No            varchar(3) NULL,
       Ware_ID              int NULL,
       MOC_No               varchar(6) NOT NULL,
       COM_No               varchar(6) NULL,
       Card_Balance         money NOT NULL DEFAULT 0.00,
       DB_Balance           money NULL DEFAULT 0.00,
       Card_SmallAccount    money NULL DEFAULT 0.00,
       Data_Type            tinyint NOT NULL DEFAULT 0,
       Remark               varchar(20) NULL,
       PRIMARY KEY (ID_KEY ASC)
)
go

IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_XF_ConsumeHistory')
CREATE INDEX IX_XF_ConsumeHistory ON dbo.XF_ConsumeHistory
(
       Consume_Time                   ASC
)
go

if not exists(select * from sysindexes where name='IX_XF_ConsumeHistory_Person_ID')
    CREATE  INDEX [IX_XF_ConsumeHistory_Person_ID] ON [dbo].[XF_ConsumeHistory]([Person_ID]) ON [PRIMARY]
go

IF OBJECT_ID('XF_ConsumeFake') IS NULL
BEGIN
   CREATE TABLE [dbo].[XF_ConsumeFake] (
   	[ID_KEY] [bigint] IDENTITY (1, 1) NOT NULL,
   	[Person_ID] [int] NOT NULL ,
   	[System_No] [varchar] (10) NOT NULL ,
   	[Consume_Mode] [int] NOT NULL ,
   	[Consume_Time] [datetime] NOT NULL ,
   	[Before_Fund] [money] NULL DEFAULT 0.00 ,
   	[Consume_Fund] [money] NOT NULL DEFAULT 0.00 ,
   	[Consume_Copy] [int] NULL DEFAULT 0,
   	[Consume_TimeLen] [float] NULL ,
   	[Consume_Capacity] [float] NULL ,
   	[Repast_No] [varchar] (3) NULL ,
   	[Ware_ID] [int] NULL ,
   	[MOC_No] [varchar] (6) NOT NULL ,
   	[COM_No] [varchar] (6) NULL ,
   	[Card_Balance] [money] NOT NULL DEFAULT 0.00,
   	[DB_Balance] [money] NULL DEFAULT 0.00,
   	[Card_SmallAccount] [money] NULL DEFAULT 0.00,
   	[Data_Type] [tinyint] NOT NULL ,
   	[SalesPerson] [varchar] (20) NULL ,
   	[Download_Time] [datetime] NULL,
    [Matching_Time] [datetime] NULL,
    PRIMARY KEY (ID_KEY ASC)
   )
END
GO

--結算后的出納操作歷史表
IF OBJECT_ID('dbo.ST_DeleteCardHistory') IS NULL
CREATE TABLE dbo.ST_DeleteCardHistory (
       ID_KEY               int IDENTITY,
       Person_ID            int NOT NULL,
       New_Date             datetime NOT NULL,
       Del_Date             datetime NOT NULL DEFAULT GETDATE(),
       Card_No              varchar(12) NOT NULL,
       Back_Fund            money NULL DEFAULT 0.00,
       Fact_Fund            MONEY DEFAULT 0.00 NOT NULL,
       Back_SmallFund       money NULL DEFAULT 0.00,
       Back_Foregift        money NULL DEFAULT 0.00,
       Card_Charge          money NULL DEFAULT 0.00,
       User_No              varchar(20) NOT NULL,
       New_User_No          varchar(20) NOT NULL,
       Remark               varchar(50) NULL,
       PRIMARY KEY (ID_KEY ASC)
)
go

IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_ST_DeleteCardHistory')
CREATE INDEX IX_ST_DeleteCardHistory ON dbo.ST_DeleteCardHistory
(
       Del_Date                       ASC
)
go

IF OBJECT_ID('dbo.ST_TransferAccountHistory') IS NULL
CREATE TABLE dbo.ST_TransferAccountHistory (
       ID_KEY               int IDENTITY,
       Person_ID            int NOT NULL,
       Transfer_Date        datetime NOT NULL DEFAULT GETDATE(),
       Transfer_Fund        money NOT NULL DEFAULT 0.00,
       Card_Balance         money NULL,
       MOC_No               varchar(6) NULL,
       User_No              varchar(20) NOT NULL,
       Remark               varchar(50) NULL,
       PRIMARY KEY (ID_KEY ASC)
)
go

IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_ST_TransferAccountHistory')
CREATE INDEX IX_ST_TransferAccountHistory ON dbo.ST_TransferAccountHistory
(
       Transfer_Date                  ASC
)
go

IF OBJECT_ID('dbo.ST_TakeFundHistory') IS NULL
CREATE TABLE dbo.ST_TakeFundHistory (
       ID_KEY               int IDENTITY,
       Person_ID            int NOT NULL,
       Take_Date            datetime NOT NULL DEFAULT GETDATE(),
       Take_Fund            money NOT NULL DEFAULT 0.00,
       Card_Balance         money NULL,
       MOC_No               varchar(6) NULL,
       User_No              varchar(20) NULL,
       Remark               varchar(50) NULL,
       PRIMARY KEY (ID_KEY ASC)
)
go

IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_ST_TakeFundHistory')
CREATE INDEX IX_ST_TakeFundHistory ON dbo.ST_TakeFundHistory
(
       Take_Date                      ASC
)
go

IF OBJECT_ID('dbo.ST_AlterCardHistory') IS NULL
CREATE TABLE dbo.ST_AlterCardHistory (
       ID_KEY               int IDENTITY,
       Person_ID            int NOT NULL,
       Alter_Date           datetime NOT NULL DEFAULT GETDATE(),
       Card_No              varchar(12) NOT NULL,
       NewCard_No           varchar(12) NULL,
       Alter_Fee            money NULL DEFAULT 0.00,
       Alter_Type           tinyint NOT NULL,
       User_No              varchar(20) NOT NULL,
       Remark               varchar(50) NULL,
       PRIMARY KEY (ID_KEY ASC)
)
go

IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_ST_AlterCardHistory')
CREATE INDEX IX_ST_AlterCardHistory ON dbo.ST_AlterCardHistory
(
       Alter_Date                      ASC
)
go

IF OBJECT_ID('dbo.ST_SupplyFundHistory') IS NULL
CREATE TABLE dbo.ST_SupplyFundHistory (
       ID_KEY               int IDENTITY,
       Person_ID            int NOT NULL,
       Supply_Date          datetime NOT NULL DEFAULT GETDATE(),
       Supply_Fund          money NOT NULL DEFAULT 0.00,
       Card_Balance         money NULL,
       MOC_No               varchar(6) NULL,
       Data_Type            tinyint NOT NULL DEFAULT 0,
       User_No              varchar(20) NOT NULL,
       Remark               varchar(50) NULL,
       PRIMARY KEY (ID_KEY ASC)
)
go

IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_ST_SupplyFundHistory')
CREATE INDEX IX_ST_SupplyFundHistory ON dbo.ST_SupplyFundHistory
(
       Supply_Date                      ASC
)
go

IF OBJECT_ID('dbo.ST_SubsidyHistory') IS NULL
CREATE TABLE dbo.ST_SubsidyHistory (
       ID_KEY               int IDENTITY,
       Person_ID            int NOT NULL,
       Subsidy_Date         datetime NOT NULL DEFAULT GETDATE(),
       Subsidy_Fund         money NOT NULL DEFAULT 0.00,
       Plan_Fund            money NULL DEFAULT 0.00,
       User_No              varchar(20) NOT NULL,
       Remark               varchar(50) NULL,
       PRIMARY KEY (ID_KEY ASC)
)
go

IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_ST_SubsidysHistory')
CREATE INDEX IX_ST_SubsidysHistory ON dbo.ST_SubsidyHistory
(
       Subsidy_Date                   ASC
)
go


--售飯與靈活消費引起的變更數據結構（增加或修改）

IF OBJECT_ID('dbo.ST_MOC') IS NULL
CREATE TABLE dbo.ST_MOC (
       MOC_No               varchar(6) NOT NULL,
       MOC_Name             varchar(20) NOT NULL,
       System_No            varchar(10) NOT NULL,
       Area_No              varchar(12) NULL,
       MOC_Type             varchar(10) NULL DEFAULT 'MOC',
       IP_Addr              varchar(15) NULL,
       Addr485              varchar(3) NULL,
       COM_No               varchar(6) NULL,
       Consume_Mode         tinyint NULL DEFAULT 1,
       Key_Type             int NULL,
       Discount_Kind        int NULL,
       Limit_Fund           money NULL,
       Limit_Deal           int NULL,
       Unit_Number          int NULL,
       Unit                 tinyint NULL,
       Unit_Price           money NULL,
       Is_FirstInput        bit NOT NULL DEFAULT 0,
       Is_Discount          bit NOT NULL DEFAULT 1,
       Is_Correct           bit NOT NULL DEFAULT 1,
       Is_UseSubsidy        bit NOT NULL DEFAULT 1,
       Is_OrderCtrl         bit NULL DEFAULT 0,
       KQ_MocType           tinyint NULL DEFAULT 1,
       Comm_Content         varchar(200) NULL,
       Comm_Status          tinyint NULL,
       KQ_UseType           tinyint NULL DEFAULT 0,
       Telphone             varchar(35) NULL,
       Remark               varchar(50) NULL,
       Download_Status      tinyint DEFAULT 0,
       Download_Time        DATETIME,
       PRIMARY KEY (MOC_No ASC)
)
go

IF OBJECT_ID('dbo.ST_SystemBalance') IS NULL
CREATE TABLE dbo.ST_SystemBalance (
       Balance_Time         datetime NOT NULL,
       Supply_AllFund       money NOT NULL DEFAULT 0,
       Take_AllFund         money NOT NULL DEFAULT 0,
       Card_Foregift        money NOT NULL DEFAULT 0,
       Card_Charge          money NOT NULL DEFAULT 0,
       Subsidy_AllFund      money NOT NULL DEFAULT 0,
       Card_AllBalance      money NOT NULL DEFAULT 0,
       Transfer_AllFund     money NOT NULL DEFAULT 0,
       Consume_AllFund      money NOT NULL DEFAULT 0,
       Remain_AllForegift   money NOT NULL DEFAULT 0,
       Back_Fund            money NOT NULL DEFAULT 0,
       Back_Foregift        money NOT NULL DEFAULT 0,
       System_Balance       money NOT NULL DEFAULT 0,
       Remark               varchar(20) NULL,
       PRIMARY KEY (Balance_Time ASC)
)
go

IF OBJECT_ID('dbo.ST_PersonAccount') IS NULL
CREATE TABLE dbo.ST_PersonAccount (
       Person_ID            int NOT NULL,
       Card_Foregift        money NOT NULL DEFAULT 0,
       Card_Charge          money NOT NULL DEFAULT 0,
       Supply_Fund          money NOT NULL DEFAULT 0,
       Subsidy_Fund         money NOT NULL DEFAULT 0,
       Take_Fund            money NOT NULL DEFAULT 0,
       AlterCard_Fund       money NOT NULL DEFAULT 0,
       Transfer_Fund        money NOT NULL DEFAULT 0,
       Account_Balance      money NOT NULL DEFAULT 0,
       Back_Foregift        money NOT NULL DEFAULT 0,
       Back_Fund            money NOT NULL DEFAULT 0,
       Back_SmallFund       money NOT NULL DEFAULT 0,
       EM_ConsumeFund       money NOT NULL DEFAULT 0,
       XF_ConsumeFund       money NOT NULL DEFAULT 0,
       Remark               varchar(20) NULL,
       PRIMARY KEY (Person_ID ASC)
)
go

IF OBJECT_ID('dbo.ST_ClearTable') IS NULL
CREATE TABLE dbo.ST_ClearTable (
       Table_Name           varchar(40) NOT NULL,
       Date_Field           varchar(40) NOT NULL,
       Clear_Index          smallint NOT NULL,
       Delete_Index         smallint NOT NULL,
       Other_Field          varchar(255) NULL,
       PRIMARY KEY (Table_Name ASC)
)
go

IF OBJECT_ID('dbo.ST_SubsidyAccount') IS NULL
CREATE TABLE dbo.ST_SubsidyAccount (
       Person_ID            int NOT NULL,
       Subsidy_Fund         money NOT NULL DEFAULT 0,
       Consume_Fund         money NOT NULL DEFAULT 0,
       Subsidy_Date         datetime NOT NULL ,
       ID_KEY               bigint NOT NULL ,
       PRIMARY KEY (Person_ID ASC)
)
go

------------------------------
------------------------------

IF OBJECT_ID('dbo.ST_DisableCard') IS NULL
   CREATE TABLE dbo.ST_DisableCard (
          ID_KEY               int IDENTITY,
          Card_No              varchar(12) NOT NULL,
          Disable_Date         datetime NULL DEFAULT GETDATE(),
          Disable_Cause        varchar(50) NULL,
          Remark               varchar(50) NULL,
          PRIMARY KEY (ID_KEY ASC)
   )
GO

IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_ST_DisableCard')
   CREATE INDEX IX_ST_DisableCard ON dbo.ST_DisableCard(Disable_Date  ASC)
GO

IF OBJECT_ID('dbo.ST_NoPersonCard') IS NULL
   CREATE TABLE dbo.ST_NoPersonCard (
          ID_KEY               int IDENTITY,
					DownLoad_Date        datetime  NOT NULL DEFAULT GETDATE(),
					Card_No              varchar(12) NOT NULL ,
					System_No            varchar(10) NULL ,
					Consume_Time         datetime  NULL ,
					Consume_Mode         int NULL ,
					Consume_Fund         money NULL ,
					Consume_Copy         int NULL ,
					Consume_TimeLen      float NULL ,
					Consume_Capacity     float NULL ,
					Repast_No            varchar(3) NULL ,
					Ware_ID              int NULL ,
					MOC_No               varchar(6) NULL ,
					COM_No               varchar(6) NULL ,
					Card_Balance         money NULL ,
					DB_Balance           money NULL DEFAULT 0.00,
					Card_SmallAccount    money NULL ,
					Data_Type            tinyint NULL ,
					Remark               varchar(50) NULL 
          PRIMARY KEY (ID_KEY ASC)
   )
GO

IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_ST_NoPersonCard')
   CREATE INDEX IX_ST_NoPersonCard ON dbo.ST_NoPersonCard(DownLoad_Date  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_EnCard') IS NULL
   CREATE TABLE dbo.ST_EnCard (
          ID_KEY               int IDENTITY,
          Card_No              varchar(40) NOT NULL,
          Remark               varchar(10) NULL,
          PRIMARY KEY (ID_KEY ASC)
   )
GO

IF OBJECT_ID('dbo.ST_SubSystem') IS NULL
   CREATE TABLE dbo.ST_SubSystem (
          System_No            varchar(10) NOT NULL,
          System_Name          varchar(20) NOT NULL,
          System_Title         varchar(20) NULL,
          System_ExeName       varchar(20) 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,
          Remark               varchar(50) NULL,
          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_Department') IS NULL
   CREATE TABLE dbo.ST_Department (
          Dept_ID              int NOT NULL,
          Dept_No              varchar(30) NULL,
          Dept_Name            varchar(30) NOT NULL,
          Dept_Chief           varchar(20) NULL,
          Dept_Total           int NULL,
          Dept_Duty            varchar(40) NULL,
          Is_Del               bit NULL DEFAULT 0,
          Remark               varchar(50) NULL,
          Add_Col1             varchar(20) NULL,
          Add_Col2             varchar(20) NULL,
          PRIMARY KEY (Dept_ID ASC)
   )
GO

IF OBJECT_ID('dbo.ST_Person') IS NULL
   CREATE TABLE dbo.ST_Person (
          Person_ID            int NOT NULL,
          Dept_ID              int NULL,
          Card_No              varchar(12) NOT NULL DEFAULT (''),
          Person_No            varchar(20) NOT NULL,
          Person_Name          varchar(20) NOT NULL,
          Person_Type          varchar(20) NULL,
          Cert_Name            varchar(20) NULL,
          Cert_Code            varchar(20) NULL,
          Duty                 varchar(20) NULL,
          Title                varchar(20) NULL,
          Nationality          varchar(2) NULL,
          Sex                  varchar(2) NULL,
          Birthday             datetime NULL,
          Is_Marry             bit NULL DEFAULT 0,
          NativePlace          varchar(50) NULL,
          Address              varchar(50) NULL,
          Residence            varchar(30) NULL,
          OnWork_Date          varchar(10) NULL,
          OffWork_Date         varchar(10) NULL,
          Is_Del               bit NOT NULL DEFAULT 0,
          Remark               varchar(50) NULL,
          Card_SN              varchar(12) NULL,
          PRIMARY KEY (Person_ID ASC)
   )
GO

IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'UQ__ST_Person__72C60C4A')
   CREATE INDEX UQ__ST_Person__72C60C4A ON dbo.ST_Person(Person_No ASC)
GO

IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'XIF1ST_Person')
   CREATE INDEX XIF1ST_Person ON dbo.ST_Person(Dept_ID ASC)
GO

IF NOT EXISTS (SELECT name FROM sysindexes where Name='IX_ST_Person_Card_No')
 CREATE  INDEX [IX_ST_Person_Card_No] ON [dbo].[ST_Person]([Card_No]) ON [PRIMARY]
GO

IF OBJECT_ID('dbo.ST_PersonPhoto') IS NULL
   CREATE TABLE dbo.ST_PersonPhoto (
       Person_ID            int NOT NULL,
       Photo                image NULL,
       PRIMARY KEY (Person_ID ASC)
)
GO


IF OBJECT_ID('dbo.ST_AlterCard') IS NULL
   CREATE TABLE dbo.ST_AlterCard (
          ID_KEY               int IDENTITY,
          Person_ID            int NOT NULL,
          Alter_Date           datetime NOT NULL DEFAULT GETDATE(),
          Card_No              varchar(12) NOT NULL,
          NewCard_No           varchar(12) NULL,
          Alter_Fee            money NULL DEFAULT 0.00,
          Alter_Type           tinyint NOT NULL,
          User_No              varchar(20) NOT NULL,
          Remark               varchar(50) NULL,
          PRIMARY KEY (ID_KEY ASC)
   )
GO

IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'XIF1ST_AlterCard')
   CREATE INDEX XIF1ST_AlterCard ON dbo.ST_AlterCard(Person_ID ASC)
GO

IF OBJECT_ID('dbo.ST_Subsidy') IS NULL
   CREATE TABLE dbo.ST_Subsidy (
          ID_KEY               int IDENTITY,
          Person_ID            int NOT NULL,
          Subsidy_Date         datetime NOT NULL DEFAULT GETDATE(),
          Subsidy_Fund         money NOT NULL DEFAULT 0.00,
          Plan_Fund            money NULL DEFAULT 0.00,
          User_No              varchar(20) NOT NULL,
          Remark               varchar(50) NULL,
          PRIMARY KEY (ID_KEY ASC)
   )
GO

IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'XIF1ST_Subsidy')
   CREATE INDEX XIF1ST_Subsidy ON dbo.ST_Subsidy(Person_ID  ASC)
GO

IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_ST_Subsidys')
   CREATE INDEX IX_ST_Subsidys ON dbo.ST_Subsidy(Subsidy_Date  ASC)
GO

IF OBJECT_ID('dbo.XF_Subsidy') IS NULL
  CREATE TABLE dbo.XF_Subsidy (
    ID_Key int IDENTITY(1, 1),
    Person_ID INT,
    Card_No VARCHAR(20),
    Apply_Time DATETIME,
    Subsidy_Fund MONEY,
    Card_Balance MONEY NULL DEFAULT 0,
    Apply_State TINYINT,
    Moc_No VARCHAR(6),
    PRIMARY KEY (ID_Key ASC)
  )
GO

IF EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_XF_Subsidy')
   DROP INDEX XF_Subsidy.IX_XF_Subsidy
GO

CREATE INDEX IX_XF_Subsidy ON dbo.XF_Subsidy
	(
	Card_No,
	Apply_Time
	) ON [PRIMARY]
GO

IF OBJECT_ID('dbo.ST_MenuInfo') IS NULL
   CREATE TABLE dbo.ST_MenuInfo (
          Menu_ID              int NOT NULL,
          System_No            varchar(20) NOT NULL,
          Menu_No              varchar(20) NOT NULL,
          Menu_Name            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_LockCard') IS NULL
   CREATE TABLE dbo.ST_LockCard (
          ID_KEY               int IDENTITY,
          Person_ID            int NOT NULL,
          Lock_Date            datetime NOT NULL DEFAULT GETDATE(),
          User_No              varchar(20) NOT NULL,
          Remark               varchar(50) NULL,
          PRIMARY KEY (ID_KEY ASC)
   )
GO

IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'XIF1ST_LockCard')
   CREATE INDEX XIF1ST_LockCard ON dbo.ST_LockCard(Person_ID ASC)
GO

IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_ST_LockCard')
   CREATE INDEX IX_ST_LockCard ON dbo.ST_LockCard(Lock_Date  ASC)
GO

IF OBJECT_ID('dbo.ST_BlackPerson') IS NULL
   CREATE TABLE dbo.ST_BlackPerson (
          ID_KEY               int IDENTITY,
          Person_ID            int NULL,
          Card_No              varchar(12) NOT NULL,
          Del_Date             datetime NULL DEFAULT GETDATE(),
          Del_Sign             bit NULL DEFAULT 0,
          Remark               varchar(50) NULL,
          PRIMARY KEY (ID_KEY ASC)
   )
GO

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

IF OBJECT_ID('dbo.ST_RoleRight') IS NULL
   CREATE TABLE dbo.ST_RoleRight (
          ID_KEY               int IDENTITY,
          Role_No              varchar(20) NOT NULL,
          Menu_ID              int NOT NULL,
          Right_Values         varchar(40) NULL,
          PRIMARY KEY (ID_KEY ASC)
   )
GO

IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'XIF1ST_RoleRight')
   CREATE INDEX XIF1ST_RoleRight ON dbo.ST_RoleRight(Role_No ASC)
GO

IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'XIF2ST_RoleRight')
   CREATE INDEX XIF2ST_RoleRight ON dbo.ST_RoleRight(Menu_ID ASC)
GO

IF OBJECT_ID('dbo.ST_UnLockCard') IS NULL
   CREATE TABLE dbo.ST_UnLockCard (
          ID_KEY               int IDENTITY,
          Person_ID            int NOT NULL,
          UnLock_Date          datetime NOT NULL DEFAULT GETDATE(),
          User_No              varchar(20) NOT NULL,
          Remark               varchar(50) NULL,
          PRIMARY KEY (ID_KEY ASC)
   )
GO

IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'XIF1ST_UnLockCard')
   CREATE INDEX XIF1ST_UnLockCard ON dbo.ST_UnLockCard(Person_ID ASC)
GO

IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_ST_UnLockCard')
   CREATE INDEX IX_ST_UnLockCard ON dbo.ST_UnLockCard(UnLock_Date ASC)
GO

IF OBJECT_ID('dbo.ST_CardType') IS NULL
   CREATE TABLE dbo.ST_CardType (
          Type_No              varchar(3) NOT NULL,
          Type_Name            varchar(20) NOT NULL,
          Min_Balance          money NOT NULL DEFAULT 0.00,
          Subsidy              money NOT NULL DEFAULT 0.00,
          Validity             int NOT NULL DEFAULT 365,
          Use_MOC              varchar(3000) NULL,
          SF_Max_Fund          money NULL DEFAULT 99999,
          SF_Max_DayFund       money NULL DEFAULT 99999,
          SF_LimitTimes        int   NOT NULL DEFAULT 0,
          Max_IcFunds          money NOT NULL DEFAULT 100,
          SF_Max_Count         int NULL DEFAULT 99999,
          SF_Discount          int NULL DEFAULT 100,
          LH_Max_Fund          money NULL DEFAULT 99999,
          LH_Max_DayFund       money NULL DEFAULT 99999,
          LH_Discount          int NULL DEFAULT 100,
          Use_Repast           varchar(3000) NULL,
          Small_Discount 	     int NULL DEFAULT 100, 
          Is_Del               bit NOT NULL DEFAULT 0,
          Is_OnlySubsidy       bit NOT NULL DEFAULT 0,
          Remark               varchar(50) NULL,
          PRIMARY KEY (Type_No ASC)
   )
GO

IF OBJECT_ID('dbo.ST_Card') IS NULL
   CREATE TABLE dbo.ST_Card (
          Person_ID            int NOT NULL,
          Type_No              varchar(3) NOT NULL,
          Card_Password        varchar(20) NULL,
          Card_NewDate         datetime NOT NULL DEFAULT GETDATE(),
          Card_ValidDate       datetime NOT NULL,
          Card_Foregift        money NOT NULL DEFAULT 0.00,
          Card_Charge          money NOT NULL DEFAULT 0.00,
          Card_Balance         money NOT NULL DEFAULT 0.00,
          Card_SmallBalance    money NULL DEFAULT 0.00,
          Card_State           tinyint NULL DEFAULT 0,
          User_No              varchar(20) NOT NULL,
          Subsidy_Fund         money NULL DEFAULT 0.00,
          Remark               varchar(50) NULL,
          Update_Time          datetime default getdate(),
          IC_Balance           money default 0,
	  Subsidy_Flag 	       tinyint default 0,
          PRIMARY KEY (Person_ID ASC) 
   )
GO

IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'XIF4ST_Card')
   CREATE INDEX XIF4ST_Card ON dbo.ST_Card(Type_No ASC)
GO

IF OBJECT_ID('dbo.ST_CardInfo') IS NULL
   CREATE TABLE dbo.ST_CardInfo (
          Card_Type            varchar(4) NOT NULL,
          Card_Foregift        money NOT NULL DEFAULT 0.00,
          Card_Charge          money NOT NULL DEFAULT 0.00,
          Passwd_Value         varchar(50) NULL,
          SmallAccount_Max     money NOT NULL,
          Subsidy_CutOffDate   datetime NULL,
          Is_DelSubsidy        bit NOT NULL DEFAULT 0,
          Day_DelSysBlack      integer NULL DEFAULT 182,
          Card_Spec            SMALLINT NOT NULL DEFAULT 0,
          Init_Code            VARCHAR(100),
          PRIMARY KEY (Card_Type ASC)
   )
GO

IF OBJECT_ID('dbo.ST_TakeFund') IS NULL
   CREATE TABLE dbo.ST_TakeFund (
          ID_KEY               int IDENTITY,
          Person_ID            int NOT NULL,
          Take_Date            datetime NOT NULL DEFAULT GETDATE(),
          Take_Fund            money NOT NULL DEFAULT 0.00,
          Card_Balance         money NULL,
          MOC_No               varchar(6) NULL,
          User_No              varchar(20) NULL,
          Remark               varchar(50) NULL,
          PRIMARY KEY (ID_KEY ASC)
   )
GO

IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'XIF1ST_TakeFund')
   CREATE INDEX XIF1ST_TakeFund ON dbo.ST_TakeFund(Person_ID ASC)
GO

IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_ST_TakeFund')
   CREATE INDEX IX_ST_TakeFund ON dbo.ST_TakeFund(Take_Date ASC)
GO

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

IF OBJECT_ID('dbo.ST_RightInfo') IS NULL
   CREATE TABLE dbo.ST_RightInfo (
          Menu_ID              int NOT NULL,
          Form_Name            varchar(50) NOT NULL,
          Right_Values         varchar(40) NULL,
          Remark               varchar(50) NULL,
          PRIMARY KEY (Menu_ID ASC) 
   )
GO

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


IF OBJECT_ID('dbo.ST_TransferAccount') IS NULL
   CREATE TABLE dbo.ST_TransferAccount (
          ID_KEY               int IDENTITY,
          Person_ID            int NOT NULL,
          Transfer_Date        datetime NOT NULL DEFAULT GETDATE(),
          Transfer_Fund        money NOT NULL DEFAULT 0.00,
          Card_Balance         money NULL,
          MOC_No               varchar(6) NULL,
          User_No              varchar(20) NOT NULL,
          Remark               varchar(50) NULL,
          PRIMARY KEY (ID_KEY ASC)
   )
GO

IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'XIF1ST_TransferAccount')
   CREATE INDEX XIF1ST_TransferAccount ON dbo.ST_TransferAccount(Person_ID ASC)
GO

IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_ST_TransferAccount')
   CREATE INDEX IX_ST_TransferAccount ON dbo.ST_TransferAccount(Transfer_Date ASC)
GO

IF OBJECT_ID('dbo.ST_OptLog') IS NULL
   CREATE TABLE dbo.ST_OptLog (
          ID_KEY               int IDENTITY,
          User_Name            varchar(20) NOT NULL,
          Opt_Computer         varchar(20) NOT NULL,
          System_No            varchar(10) NOT NULL,
          Opt_Table            varchar(20) NOT NULL,
          Opt_Type             varchar(20) NOT NULL,
          Opt_Time             datetime NOT NULL DEFAULT GETDATE(),
          Opt_Info             varchar(255) NOT NULL,
          Remark               varchar(50) NULL,
          PRIMARY KEY (ID_KEY ASC)
   )
GO

IF OBJECT_ID('dbo.ST_UserInfo') IS NULL
   CREATE TABLE dbo.ST_UserInfo (
          User_No              varchar(20) NOT NULL,
          User_Name            varchar(20) NOT NULL,
          User_Pwd             varchar(20) NULL,
          User_Type            tinyint NULL DEFAULT 0,
          Card_No              varchar(12) NULL,
          User_Moc             varchar(200) 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,
          Current_Menu         varchar(3)   NULL DEFAULT 'ST',
          Remark               varchar(50) NULL,
          PRIMARY KEY (User_No ASC)
   )
GO

IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'XIF1ST_UserInfo')
   CREATE INDEX XIF1ST_UserInfo ON dbo.ST_UserInfo(Role_No  ASC)
GO

IF OBJECT_ID('dbo.ST_DeleteCard') IS NULL
   CREATE TABLE dbo.ST_DeleteCard (
          ID_KEY               int IDENTITY,
          Person_ID            int NOT NULL,
          New_Date             datetime NOT NULL,
          Del_Date             datetime NOT NULL DEFAULT GETDATE(),
          Card_No              varchar(12) NOT NULL,
          Back_Fund            money NULL DEFAULT 0.00,
          Fact_Fund            MONEY DEFAULT 0.00 NOT NULL,
          Back_SmallFund       money NULL DEFAULT 0.00,
          Back_Foregift        money NULL DEFAULT 0.00,
          Card_Charge          money NULL DEFAULT 0.00,
          User_No              varchar(20) NOT NULL,
          New_User_No          varchar(20) NOT NULL,          
          Remark               varchar(50) NULL,
          PRIMARY KEY (ID_KEY ASC)
   )
GO

IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'XIF1ST_DeleteCard')
   CREATE INDEX XIF1ST_DeleteCard ON dbo.ST_DeleteCard(Person_ID ASC)
GO

IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_EM_DelAccount')
   CREATE INDEX IX_EM_DelAccount ON dbo.ST_DeleteCard(Del_Date ASC)
GO

IF OBJECT_ID('dbo.ST_Dictionary') IS NULL
   CREATE TABLE dbo.ST_Dictionary (
          ID_KEY               int IDENTITY,
          Table_Name           varchar(20) NOT NULL,
          Table_ChsName        varchar(40) NULL,
          Dict_Id              varchar(20) NULL,
          Dict_Value           varchar(40) NULL,
          Is_SysCol            bit NULL DEFAULT 0,
          Remark               varchar(50) NULL,
          PRIMARY KEY (ID_KEY ASC)
   )
GO

IF OBJECT_ID('dbo.ST_DeptRight') IS NULL
   CREATE TABLE dbo.ST_DeptRight (
          User_No              varchar(20) NOT NULL,
          Dept_ID              int NOT NULL,
          Check_State          tinyint NOT NULL DEFAULT 0,
          Remark               varchar(50) NULL,
          PRIMARY KEY (User_No ASC, Dept_ID ASC)
   )
GO

IF OBJECT_ID('dbo.ST_SubSysRight') IS NULL
   CREATE TABLE dbo.ST_SubSysRight (
          Person_ID            int NOT NULL,
          System_No            varchar(10) NOT NULL,
          Remark               varchar(50) NULL,
          PRIMARY KEY (Person_ID ASC, System_No ASC)
   )
GO

IF OBJECT_ID('dbo.ST_SupplyFund') IS NULL
   CREATE TABLE dbo.ST_SupplyFund (
          ID_KEY               int IDENTITY,
          Person_ID            int NOT NULL,
          Supply_Date          datetime NOT NULL DEFAULT GETDATE(),
          Supply_Fund          money NOT NULL DEFAULT 0.00,
          Card_Balance         money NULL,
          MOC_No               varchar(6) NULL,
          Data_Type            tinyint NOT NULL DEFAULT 0,
          User_No              varchar(20) NOT NULL,
          Remark               varchar(50) NULL,
          PRIMARY KEY (ID_KEY ASC)
   )
GO

IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'XIF1ST_SupplyFund')
   CREATE INDEX XIF1ST_SupplyFund ON dbo.ST_SupplyFund(Person_ID ASC)
GO

IF OBJECT_ID('dbo.ST_ClearData') IS NULL
   CREATE TABLE dbo.ST_ClearData (
          Person_ID            int NOT NULL,
          Card_Foregift        money NULL DEFAULT 0.00,
          Card_Charge          money NULL DEFAULT 0.00,
          Supply_Fund          money NULL DEFAULT 0.00,
          Subsidy_Fund         money NULL DEFAULT 0.00,
          Fund                 money NULL DEFAULT 0.00,
          Take_Fund            money NULL DEFAULT 0.00,
          Back_Fund            money NULL DEFAULT 0.00,
          Back_Foregift        money NULL DEFAULT 0.00,
          Transfer_Fund        money NULL DEFAULT 0.00,
          Remark               varchar(50) NULL,
          PRIMARY KEY (Person_ID ASC)
   )
GO

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

IF OBJECT_ID('dbo.ST_NoBalance') IS NULL
   CREATE TABLE dbo.ST_NoBalance (
          NoBalance_ID         int NOT NULL,
          Card_No              varchar(12) NULL,
          Card_Balance         money NULL DEFAULT 0.00,
          DataBase_Balance     money NULL DEFAULT 0.00,
          Adjust_Fund          money NULL DEFAULT 0.00,
          Opt_Project          varchar(40) NULL,
          Opt_Time             datetime NULL DEFAULT GETDATE(),
          User_No              varchar(20) NOT NULL,
          Remark               varchar(50) NULL,
          PRIMARY KEY (NoBalance_ID ASC)
   )
GO

IF OBJECT_ID('dbo.ST_SelfMenu') IS NULL
    CREATE TABLE dbo.ST_SelfMenu (
          User_No              varchar(20) NOT NULL,
          Menu_ID              int NOT NULL,
          PRIMARY KEY (User_No ASC, Menu_ID ASC)
     )
GO

--1.初始化本系統的參數
INSERT INTO dbo.ST_SubSystem(System_No,System_Name,System_ExeName,Index_No)
SELECT * FROM (SELECT 'ST'[System_No],'一卡通平台'[System_Name],'STONECARD.exe'[System_ExeName],0[Index_No]                
              ) A
WHERE A.System_No NOT IN(SELECT System_No FROM dbo.ST_SubSystem)
GO
