INSERT INTO dbo.ST_SubSystem(System_No,System_Name,System_ExeName,Index_No)
SELECT * FROM (SELECT 'DC'[System_No],'订餐辅助系统'[System_Name],'OrderMeal.exe'[System_ExeName],9[Index_No]
              ) A
WHERE A.System_No NOT IN(SELECT System_No FROM dbo.ST_SubSystem)
GO

IF OBJECT_ID('SF_OrderArea') IS NULL
   CREATE TABLE [dbo].[SF_OrderArea] (
	   [Area_No] [varchar] (6) NOT NULL ,
	   [Area_Name] [varchar] (20) NULL ,
	   [Note] [varchar] (50) NULL, 
       PRIMARY KEY (Area_No ASC)
   )
GO

IF OBJECT_ID('SF_OrderMoc') IS NULL
   CREATE TABLE [dbo].[SF_OrderMoc] (
	   [Area_No] [varchar] (6) NULL ,
	   [Moc_No] [varchar] (6) NOT NULL ,
	   [Moc_Addr] [varchar] (15) NULL ,
	   [Note] [varchar] (50) NULL,
       PRIMARY KEY (Moc_No ASC)  
   )
GO

IF OBJECT_ID('SF_OrderRule') IS NULL
   CREATE TABLE [dbo].[SF_OrderRule] (
     [id_Key] [int] IDENTITY (1, 1) NOT NULL ,
	   [Moc_No] [varchar] (100) NULL ,
	   [Sk_BeginTime] [varchar] (5) NULL ,
	   [Sk_EndTime] [varchar] (5) NULL ,
	   [Order_Kind] [int] NULL ,
	   [Order_Repast] [varchar] (50) NULL ,
	   [Note] [varchar] (50) NULL,
	   PRIMARY KEY (ID_KEY ASC) 
   )
GO

IF OBJECT_ID('SF_OrderData') IS NULL
   CREATE TABLE [dbo].[SF_OrderData] (
   	 [id_Key] [int] IDENTITY (1, 1) NOT NULL ,
	   [Person_ID] [int] NULL ,
	   [Card_No] [varchar] (10) NULL ,
	   [Order_date] [varchar] (10) NULL ,
	   [Repast_No] [varchar] (3) NULL ,
	   [Moc_No] [varchar] (6) NULL, 
	   [Note] [varchar] (50) NULL,
	   PRIMARY KEY (ID_KEY ASC)
   ) 
GO

IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_SF_OrderData')
   CREATE  INDEX [IX_SF_OrderData] ON [dbo].[SF_OrderData]([Person_ID], [Order_date]) ON [PRIMARY]
GO


IF OBJECT_ID('SF_OrderUnUseData') IS NULL
   CREATE TABLE [dbo].[SF_OrderUnUseData](
       [Id_key] [int] IDENTITY (1, 1) NOT NULL ,
       [Person_Id] [int] NULL ,
       [Order_Date] [varchar] (20) NULL,
       [Repast_No] [varchar] (10) NULL,
       PRIMARY KEY (ID_KEY ASC) 
   )
GO

--2009.11.5 实时订餐系统增加
IF OBJECT_ID('DC_Repast') IS NULL
   CREATE TABLE DC_Repast(
      Repast_No  VARCHAR(3) NOT NULL,
      Bgn_Time   VARCHAR(5) NOT NULL,
      End_Time   VARCHAR(5) NOT NULL,
      Order_Days TINYINT NOT NULL DEFAULT 0,
      Can_Order  BIT NOT NULL DEFAULT 1,
      PRIMARY KEY (Repast_No ASC)
   )
GO

IF OBJECT_ID('DC_OrderData') IS NULL
   CREATE TABLE DC_OrderData (
      ID_KEY        INT IDENTITY(1,1) NOT NULL,
      Person_ID     INT NOT NULL,
      Order_Date    VARCHAR(10) NOT NULL,
      Order_Time    DATETIME NOT NULL,
      Repast_No     VARCHAR(3) NOT NULL,
      Moc_No_DC     VARCHAR(6) NOT NULL,
      Data_Type     TINYINT NOT NULL DEFAULT 0,
      Download_Time DATETIME NULL,
      Consume_Time  DATETIME NULL, 
      Moc_No_SF     VARCHAR(6) NULL,
      Remark        VARCHAR(40) NULL,
      PRIMARY KEY (ID_KEY ASC)
   ) 
GO

IF NOT EXISTS (SELECT name FROM sysindexes WHERE name = 'IX_DC_OrderData')  
   CREATE NONCLUSTERED INDEX IX_DC_OrderData ON dbo.DC_OrderData
	   (
	   Person_ID,
	   Order_Date,
	   Repast_No
	   ) ON [PRIMARY]
GO

IF OBJECT_ID('DC_OrderDataLog') IS NULL
   CREATE TABLE DC_OrderDataLog (
      ID_KEY        INT IDENTITY(1,1) NOT NULL,
      Person_ID     INT NOT NULL,
      Order_Date    VARCHAR(10) NOT NULL,
      Order_Time    DATETIME NOT NULL,
      Repast_No     VARCHAR(3) NOT NULL,
      Moc_No_DC     VARCHAR(6) NOT NULL,
      Data_Type     TINYINT NOT NULL DEFAULT 0,
      Download_Time DATETIME NULL,
      Consume_Time  DATETIME NULL, 
      Moc_No_SF     VARCHAR(6) NULL,
      Remark        VARCHAR(40) NULL,
      Card_Balance  MONEY NOT NULL,
      DB_Balance    MONEY NOT NULL,
      Consume_Fund  MONEY NOT NULL,
      Del_Date	    DATETIME NOT NULL,
      User_Name     VARCHAR(20) NOT NULL,
      PRIMARY KEY (ID_KEY ASC)
   ) 
GO
