--售飯與靈活消費的公用表結構
IF OBJECT_ID('dbo.XF_SysParam') IS NULL
CREATE TABLE dbo.XF_SysParam (
       Param_ID             int IDENTITY,
       Param_Code           varchar(20) NOT NULL,
       Param_Name           varchar(40) NOT NULL,
       Param_Value          varchar(100) NOT NULL,
       PRIMARY KEY (Param_ID ASC)
)
go

IF OBJECT_ID('dbo.XF_Collect') IS NULL
CREATE TABLE dbo.XF_Collect (
       ID_KEY               int IDENTITY,
       IP_Addr              varchar(15) NULL,
       Com_Port             int NULL,
       Chg_Status           tinyint NULL,
       PRIMARY KEY (ID_KEY ASC)
)
go

IF OBJECT_ID('dbo.XF_Section') IS NULL
CREATE TABLE dbo.XF_Section (
       Sect_No              varchar(4) NOT NULL,
       Sect_Name            varchar(20) NOT NULL,
       Remark               varchar(20) NULL,
       PRIMARY KEY (Sect_No ASC)
)
go

IF OBJECT_ID('dbo.XF_Area') IS NULL
CREATE TABLE dbo.XF_Area (
       Area_No              varchar(12) NOT NULL,
       Sect_No              varchar(4)  NOT NULL,
       Area_Name            varchar(30) NOT NULL,
       Remark               varchar(20) NULL,
       PRIMARY KEY (Area_No ASC), 
       FOREIGN KEY (Sect_No)
                             REFERENCES dbo.XF_Section  (Sect_No)
                             ON DELETE NO ACTION
                             ON UPDATE NO ACTION
)
go

IF OBJECT_ID('dbo.XF_KeyType') IS NULL
CREATE TABLE XF_KeyType (
       Key_Type             int NOT NULL,
       Key_Value            int NOT NULL,
       Key_TypeName         varchar(20) NOT NULL,
       Key_Fund             money NOT NULL DEFAULT 0.00,
       Remark               varchar(20) NULL,
       PRIMARY KEY (Key_Type ASC, Key_Value ASC)
)
go

IF OBJECT_ID('dbo.XF_WareInfo') IS NULL
CREATE TABLE dbo.XF_WareInfo (
       Ware_ID              int NOT NULL,
       System_No            varchar(10) NOT NULL,
       Ware_No              varchar(20) NOT NULL,
       Ware_Name            varchar(50) NOT NULL,
       Ware_Price           money NOT NULL,
       Ware_Discount        tinyint NOT NULL DEFAULT 100,
       Ware_Unit            varchar(8) NULL,
       Remark               varchar(30) NULL,
       PRIMARY KEY (Ware_ID ASC, System_No ASC)
)
go

IF OBJECT_ID('XF_ConsumeCleanup') IS NULL
CREATE TABLE [dbo].[XF_ConsumeCleanup] (
	[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 ,
	[Consume_Fund] [money] NOT 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) NOT NULL ,
	[COM_No] [varchar] (6) NULL ,
	[Card_Balance] [money] NOT NULL ,
	[DB_Balance] [money] NULL ,
	[Card_SmallAccount] [money] NULL ,
	[Data_Type] [tinyint] NOT NULL ,
	[Remark] [varchar] (20) NULL ,
	[SalesPerson] [varchar] (20) NULL ,
	[Download_Time] [datetime] NULL,
    PRIMARY KEY (ID_KEY ASC) 
)
GO

IF OBJECT_ID('XF_CollectLog') IS NULL
 CREATE TABLE XF_CollectLog(Id_Key int IDENTITY(1,1) NOT NULL,Log_DateTime DateTime,Log_Msg varchar(255),PRIMARY KEY (ID_KEY ASC))  
GO

--靈活消費表
IF OBJECT_ID('dbo.LH_GetConsume') IS NULL
CREATE TABLE dbo.LH_GetConsume (
       ID_Key               int IDENTITY,
       Card_No              varchar(12) NULL,
       Moc_No               int NOT NULL,
       Get_Time             datetime NOT NULL,
       Consume_AllTime      float NULL,
       Consume_AllCopy      int NULL,
       Consume_AllFund      money NOT NULL,
       Consume_AllCapacity  float NULL,
       User_Name            varchar(20) NULL,
       Remark               varchar(20) NULL,
       PRIMARY KEY (ID_Key ASC)
)
go

IF OBJECT_ID('dbo.LH_DiscountKind') IS NULL
CREATE TABLE dbo.LH_DiscountKind (
       Discount_Kind        int IDENTITY,
       Discount_Name        varchar(30) NOT NULL,
       Default_ConsumeFund  money NULL,
       Alarm_OverTime       int NULL,
       Discount_Option      int NULL DEFAULT 0,
       Is_LeastConsume      bit NULL,
       LeastConsume_Fund    money NULL,
       Remark               varchar(20) NULL,
       PRIMARY KEY (Discount_Kind ASC)
)
go

IF OBJECT_ID('dbo.LH_TimePartDiscount') IS NULL
CREATE TABLE dbo.LH_TimePartDiscount (
       ID_KEY               int IDENTITY,
       Discount_Kind        int NOT NULL,
       Bgn_Time             varchar(5) NOT NULL,
       End_Time             varchar(5) NOT NULL,
       Discount_Num         int NOT NULL,
       Remark               varchar(20) NULL,
       PRIMARY KEY (ID_KEY ASC) 
)
go

IF OBJECT_ID('dbo.LH_MinuteDiscount') IS NULL
CREATE TABLE dbo.LH_MinuteDiscount (
       ID_KEY               int IDENTITY,
       Discount_Kind        int NOT NULL,
       Bgn_Minute           int NOT NULL,
       End_Minute           int NOT NULL,
       Discount_Minute      int NOT NULL,
       Remark               varchar(20) NULL,
       PRIMARY KEY (ID_KEY ASC)
)
go

IF OBJECT_ID('dbo.LH_TimeLenDiscount') IS NULL
CREATE TABLE dbo.LH_TimeLenDiscount (
       ID_KEY               int IDENTITY,
       Discount_Kind        int NOT NULL,
       Bgn_TimeLen          float NOT NULL,
       End_TimeLen          float NOT NULL,
       Discount_Num         int NOT NULL,
       Remark               varchar(20) NULL,
       PRIMARY KEY (ID_KEY ASC)
)
go

IF OBJECT_ID('dbo.LH_FeastDiscount') IS NULL
CREATE TABLE dbo.LH_FeastDiscount (
       ID_KEY               int IDENTITY,
       Discount_Kind        int NOT NULL,
       Feast_Date           varchar(10) NOT NULL,
       Discount_Num         int NOT NULL,
       Remark               varchar(20) NULL,
       PRIMARY KEY (ID_KEY ASC)
)
go

IF OBJECT_ID('dbo.LH_WeekDiscount') IS NULL
CREATE TABLE dbo.LH_WeekDiscount (
       ID_KEY          int IDENTITY,
       Discount_Kind        int NOT NULL,
       Week_No              int NOT NULL,
       Discount_Num         int NOT NULL,
       Remark               varchar(20) NULL,
       PRIMARY KEY (ID_KEY ASC)
)
go

--時長刷卡表（收集端增加的表）
IF OBJECT_ID('dbo.LH_TimeBrushCard') IS NULL
CREATE TABLE dbo.LH_TimeBrushCard (
       Card_No              varchar(12) NOT NULL,
       Brush_Time           datetime NOT NULL,
       PRIMARY KEY (Card_No ASC)
)
go

--設置參數卡中的參數列表
IF OBJECT_ID('dbo.LH_SetParamCard') IS NULL
CREATE TABLE dbo.LH_SetParamCard (
	Mny_Index tinyint NOT NULL ,
	Week_No   tinyint NOT NULL ,
	Bgn_Time  varchar(5) NOT NULL ,
	End_Time  varchar(5) NOT NULL ,
	Unit_Fund money NOT NULL ,
	PRIMARY KEY (Mny_Index)
)
GO

--脫機小金額應用的消費場合
IF OBJECT_ID('dbo.LH_ConsumeArea') IS NULL
CREATE TABLE dbo.LH_ConsumeArea (
  Area_No              varchar(12) NOT NULL,
	Area_Name            varchar(30) NOT NULL,
	Area_Use             int         NOT NULL,
	Range_Bgn            int         NOT NULL,
	Range_End            int         NOT NULL,	
	Remark               varchar(20) NULL,
	PRIMARY KEY (Area_No ASC)	
)
GO
