IF NOT EXISTS(
   SELECT * 
    FROM syscolumns 
    WHERE id=object_id('T_EXPENSECARD') and Name='F_UniqueCodeClone')
BEGIN  
        ALTER TABLE T_EXPENSECARD ADD F_UniqueCodeClone varchar(50)
       
END

GO

IF OBJECT_ID (N'T_TicketTypeStyle') IS NULL
BEGIN  --如果不存在该表，则进行创建
CREATE TABLE [dbo].[T_TicketTypeStyle](
	[F_ID] [varchar](10) NOT NULL,
	[F_NAME] [varchar](20) NOT NULL,
	[F_ORDERINDEX] [int] NOT NULL,
	[F_AVAILABLE] [int] NOT NULL
) ON [PRIMARY]

END
GO


IF NOT EXISTS(
   SELECT * 
    FROM syscolumns 
    WHERE id=object_id('T_TicketType') and Name='F_StyleName')
BEGIN  
        ALTER TABLE T_TicketType ADD F_StyleName varchar(20) 
END
GO



IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'V_TICKETFORCHECKOUT')
BEGIN   
  DROP View V_TICKETFORCHECKOUT;
END

GO
CREATE VIEW V_TICKETFORCHECKOUT AS 
SELECT   dbo.T_TICKET.F_ID, dbo.T_TICKET.F_SNID, dbo.T_TICKET.F_SENDSHOPID, dbo.T_TICKET.F_MONEY, 
                dbo.T_TICKET.F_OLDMONEY, dbo.T_TICKET.F_CHECKOUTID, dbo.T_TICKET.F_TYPE AS T_TYPEID, 
                dbo.T_CHECKOUT.F_DATETIME, dbo.T_CHECKOUT.F_SHOPID, dbo.T_CHECKOUT.F_USERID, 
                dbo.T_GOODS.F_ID AS F_GOODSID, dbo.T_GOODS.F_NAME AS F_GOODSNAME, 
                dbo.T_TICKETTYPE.F_NAME AS T_TYPENAME, dbo.T_TICKETTYPE.F_StyleName As F_TYPESTYLENAME
FROM      dbo.T_TICKET INNER JOIN
                dbo.T_CHECKOUT ON dbo.T_CHECKOUT.F_ID = dbo.T_TICKET.F_CHECKOUTID INNER JOIN
                dbo.T_TICKETTYPE ON dbo.T_TICKET.F_TYPE = dbo.T_TICKETTYPE.F_ID LEFT OUTER JOIN
                dbo.T_CONSUME ON dbo.T_CONSUME.F_ID = dbo.T_TICKET.F_CONSUMEID LEFT OUTER JOIN
                dbo.T_GOODS ON dbo.T_GOODS.F_ID = dbo.T_CONSUME.F_GOODSID


GO


IF NOT EXISTS(
   SELECT * 
    FROM syscolumns 
    WHERE id=object_id('T_GOODS') and Name='F_IsPackage')
BEGIN  
        ALTER TABLE T_GOODS ADD F_IsPackage smallint NULL DEFAULT 0 
END
GO


IF NOT EXISTS(
   SELECT * 
    FROM syscolumns 
    WHERE id=object_id('T_TICKET') and Name='F_PackageTicketIssuingID')
BEGIN  
        ALTER TABLE T_TICKET ADD F_PackageTicketIssuingID varchar(20)  
END
GO




--套票管理
IF OBJECT_ID (N'T_PackageTicket') IS NULL
BEGIN  --如果不存在该表，则进行创建
CREATE TABLE [dbo].[T_PackageTicket](
	[F_ID] [varchar](20) NOT NULL,
	[F_GoodsID] [varchar](30) NOT NULL,
	[F_ValidityDay] [int] NULL,
        [F_MODIFYUSERID] [varchar](20) NULL,
        [F_CREATEUSERID] [varchar](20) NULL,
        [F_MODIFYTIME] [datetime] NULL,
        [F_CREATETIME] [datetime] NULL,
        [F_AVAILABLE] [smallint] NOT NULL DEFAULT 0,
	[F_SHOPID] [varchar](10) NULL,
 CONSTRAINT [PK_T_PackageTicket] PRIMARY KEY CLUSTERED 
(
	[F_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

SET ANSI_PADDING OFF

ALTER TABLE [dbo].[T_PackageTicket]  WITH CHECK ADD  CONSTRAINT [FK_套票店号] FOREIGN KEY([F_SHOPID])
REFERENCES [dbo].[T_SHOP] ([F_ID])

ALTER TABLE [dbo].[T_PackageTicket] CHECK CONSTRAINT [FK_套票店号]

ALTER TABLE [dbo].[T_PackageTicket]  WITH CHECK ADD  CONSTRAINT [FK_套票外键项目编号] FOREIGN KEY([F_GoodsID])
REFERENCES [dbo].[T_GOODS] ([F_ID])

ALTER TABLE [dbo].[T_PackageTicket] CHECK CONSTRAINT [FK_套票外键项目编号]

END
GO



--套票消费券
IF OBJECT_ID (N'T_PackageTicketType') IS NULL
BEGIN  --如果不存在该表，则进行创建
CREATE TABLE [dbo].[T_PackageTicketType](
	[F_ID] [varchar](20) NOT NULL,
	[F_PackageTicketID] [varchar](20) NOT NULL,
	[F_TicketTypeID] [varchar](10) NOT NULL,
	[F_Amount] [int] NOT NULL,
 CONSTRAINT [PK_T_PackageTicketType] PRIMARY KEY CLUSTERED 
(
	[F_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


SET ANSI_PADDING OFF

ALTER TABLE [dbo].[T_PackageTicketType] ADD  CONSTRAINT [DF_T_PackageTicketType_F_Amount]  DEFAULT ((1)) FOR [F_Amount]

ALTER TABLE [dbo].[T_PackageTicketType]  WITH CHECK ADD  CONSTRAINT [FK_套票券类型编号] FOREIGN KEY([F_TicketTypeID])
REFERENCES [dbo].[T_TICKETTYPE] ([F_ID])

ALTER TABLE [dbo].[T_PackageTicketType] CHECK CONSTRAINT [FK_套票券类型编号]

ALTER TABLE [dbo].[T_PackageTicketType]  WITH CHECK ADD  CONSTRAINT [FK_套票券外键套票编号] FOREIGN KEY([F_PackageTicketID])
REFERENCES [dbo].[T_PackageTicket] ([F_ID])

ALTER TABLE [dbo].[T_PackageTicketType] CHECK CONSTRAINT [FK_套票券外键套票编号]

END
GO


--套票发放
IF OBJECT_ID (N'T_PackageTicketIssuing') IS NULL
BEGIN  --如果不存在该表，则进行创建
CREATE TABLE [dbo].[T_PackageTicketIssuing](
	[F_ID] [varchar](20) NOT NULL,
	[F_PackageTicketID] [varchar](20) NOT NULL,
	[F_ExpenseCardID] [varchar](20) NULL,
	[F_HANDBRANDID] [varchar](10) NULL,
	[F_ClientID] [varchar](20) NULL,
	[F_STATE] [varchar](10) NULL,
	[F_SendDateTime] [datetime] NULL,
	[F_UserID] [varchar](20) NULL,
	[F_ValidityDay] [int] NULL,
	[F_EndDateTime] [datetime] NULL,
	[F_UsedDateTime] [datetime] NULL,
	[F_SHOPID] [varchar](10) NULL,
	[F_CONSUMEID] [varchar](20) NULL,
	[F_AMOUNT] [DECIMAL](9,2) NULL,
	[F_GOODSNAME] [varchar](100) NULL,
 CONSTRAINT [PK_T_PackageTicketIssuing] PRIMARY KEY CLUSTERED 
(
	[F_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


SET ANSI_PADDING OFF

ALTER TABLE [dbo].[T_PackageTicketIssuing]  WITH CHECK ADD  CONSTRAINT [FK_套票发放外键操作员] FOREIGN KEY([F_UserID])
REFERENCES [dbo].[T_USER] ([F_USERNAME])

ALTER TABLE [dbo].[T_PackageTicketIssuing] CHECK CONSTRAINT [FK_套票发放外键操作员]

ALTER TABLE [dbo].[T_PackageTicketIssuing]  WITH CHECK ADD  CONSTRAINT [FK_套票发放外键店号] FOREIGN KEY([F_SHOPID])
REFERENCES [dbo].[T_SHOP] ([F_ID])

ALTER TABLE [dbo].[T_PackageTicketIssuing] CHECK CONSTRAINT [FK_套票发放外键店号]

ALTER TABLE [dbo].[T_PackageTicketIssuing]  WITH CHECK ADD  CONSTRAINT [FK_外键套票类型编号] FOREIGN KEY([F_PackageTicketID])
REFERENCES [dbo].[T_PackageTicket] ([F_ID])

ALTER TABLE [dbo].[T_PackageTicketIssuing] CHECK CONSTRAINT [FK_外键套票类型编号]

END
GO





--消费券可用项目管理
IF OBJECT_ID (N'T_TICKETTYPEGOODS') IS NULL
BEGIN  --如果不存在该表，则进行创建
CREATE TABLE [dbo].[T_TICKETTYPEGOODS](
	[F_ID] [varchar](20) NOT NULL,
	[F_TICKETTYPEID] [varchar](10) NULL,
	[F_GOODSID] [varchar](30) NULL,
 CONSTRAINT [PK_T_TICKETTYPEGOODS] PRIMARY KEY CLUSTERED 
(
	[F_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


SET ANSI_PADDING OFF

ALTER TABLE [dbo].[T_TICKETTYPEGOODS]  WITH CHECK ADD  CONSTRAINT [FK_外键项目编号] FOREIGN KEY([F_GOODSID])
REFERENCES [dbo].[T_GOODS] ([F_ID])

ALTER TABLE [dbo].[T_TICKETTYPEGOODS] CHECK CONSTRAINT [FK_外键项目编号]

ALTER TABLE [dbo].[T_TICKETTYPEGOODS]  WITH CHECK ADD  CONSTRAINT [FK_外键消费券类型编号] FOREIGN KEY([F_TICKETTYPEID])
REFERENCES [dbo].[T_TICKETTYPE] ([F_ID])

ALTER TABLE [dbo].[T_TICKETTYPEGOODS] CHECK CONSTRAINT [FK_外键消费券类型编号]


END
GO



IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'V_PackageTicketType')
BEGIN   
  DROP View V_PackageTicketType;
END

GO
--创建视图
CREATE VIEW V_PackageTicketType AS 
SELECT   dbo.T_TICKETTYPE.F_NAME, 
         dbo.T_PackageTicketType.F_ID, 
		 dbo.T_PackageTicketType.F_TicketTypeID, 
         dbo.T_PackageTicketType.F_Amount, 
		 dbo.T_PackageTicketType.F_PackageTicketID
FROM      dbo.T_PackageTicketType 
INNER JOIN
dbo.T_TICKETTYPE ON dbo.T_PackageTicketType.F_TicketTypeID = dbo.T_TICKETTYPE.F_ID



GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'V_TICKETPackage')
BEGIN   
  DROP View V_TICKETPackage;
END

GO
--创建视图


CREATE VIEW [dbo].[V_TICKETPackage]
AS
SELECT  T_TICKET.F_SHOPID ,
            T_TICKET.F_SNID ,
            T_TICKET.F_ID ,
            T_TICKET.F_MONEY ,
            T_TICKET.F_STARTDATE ,
            T_TICKET.F_ENDDATE ,
            T_TICKET.F_STATE ,
            T_TICKET.F_SENDUSER ,
            T_TICKET.F_SENDDATE ,
            T_TICKET.F_SENDSHOPID ,
            T_TICKET.F_CHECKOUTID ,
            T_TICKET.F_CHECKOUTSHOPID ,
            T_TICKET.F_CHECKOUTTIME ,
            T_TICKET.F_CHECKOUTUSER ,
            T_TICKET.F_CLIENTID ,
            T_TICKET.F_PHONE ,
            T_TICKET.F_NAME ,
            T_TICKET.F_REMARK ,
            T_TICKET.F_TYPE ,
            T_TICKET.F_CONSUMEID ,
            T_TICKET.F_OLDMONEY ,
            T_TICKET.F_RECEIVEID ,
            T_TICKET.F_ClientPromotionID ,
            T_TICKET.F_GivingCount ,
            T_TICKET.F_OpenID ,
            T_TICKET.F_TYPE AS F_TICKETTYPEID ,
            T_PackageTicketIssuing.F_ExpenseCardID ,
            T_PackageTicketIssuing.F_HANDBRANDID ,
            T_PackageTicketIssuing.F_ClientID AS F_PackageTicketIssuingCardId ,
            T_PackageTicket.F_ID AS F_PackageTicketID
    FROM    T_TICKET
            INNER JOIN T_PackageTicketIssuing ON ( dbo.T_TICKET.F_PackageTicketIssuingID = T_PackageTicketIssuing.F_ID )
            INNER JOIN T_PackageTicket ON ( dbo.T_PackageTicketIssuing.F_PackageTicketID = T_PackageTicket.F_ID )


GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'V_GOODS')
BEGIN   
  DROP View V_GOODS;
END

GO

CREATE VIEW V_GOODS
AS
    SELECT  dbo.T_GOODS.F_ID ,
            dbo.T_GOODS.F_NAME ,
            dbo.T_GOODS.F_UNITID ,
            dbo.T_UNIT.F_NAME AS F_UNITNAME ,
            dbo.T_GOODS.F_PRICE ,
            dbo.T_GOODS.F_CANREBATE ,
            dbo.T_GOODS.F_REMARK ,
            dbo.T_GOODS.F_GOODSTYPEID ,
            dbo.T_GOODSTYPE.F_NAME AS F_GOODSTYPENAME ,
            dbo.T_GOODS.F_COST ,
            dbo.T_GOODS.F_COSTTYPE ,
            dbo.T_GOODS.F_STARTTIMES ,
            dbo.T_GOODS.F_STARTTIMESPRICE ,
            dbo.T_GOODS.F_TIMES ,
            dbo.T_GOODS.F_TIMESPRICE ,
            dbo.T_GOODS.F_GOODSTYPE1ID ,
            dbo.T_GOODSTYPE1.F_NAME AS F_GOODSTYPE1NAME ,
            dbo.T_GOODSTYPE1.F_STATE ,
            dbo.T_GOODSTYPE1.F_ISSTORAGE ,
            dbo.T_GOODSTYPE1.F_ISSALE ,
            dbo.T_GOODSTYPE1.F_INTEGRAL ,
            dbo.T_GOODS.F_NAMEABC ,
            dbo.T_GOODS.F_SAFEAMOUNT ,
            dbo.T_GOODS.F_VIPPRICE ,
            dbo.T_GOODS.F_SHOPID ,
            dbo.T_GOODS.F_ISINPUTDEDUCT ,
            dbo.T_GOODS.F_TIMINGTYPE ,
            dbo.T_GOODS.F_FALLTIME ,
            dbo.T_GOODS.F_ISSMALL ,
            dbo.T_GOODS.F_HEADSHIPFLAG ,
            dbo.T_GOODS.F_HEADSHIPTRAIT ,
            dbo.T_GOODS.F_POLYPHONE ,
            dbo.T_GOODS.F_ISOVERTIME ,
            dbo.T_GOODS.F_HUMANAMOUNT ,
            dbo.T_GOODS.F_ISSHOW ,
            dbo.T_GOODS.F_ISCLEANWATER ,
            dbo.T_GOODS.F_ISEXTERNALSOUND ,
            dbo.T_GOODS.F_CASEHISTORYTYPE ,
            dbo.T_GOODS.F_TIMEKEEPING ,
            dbo.T_GOODS.F_SHOWID ,
            dbo.T_GOODS.F_AVAILABLE ,
            dbo.T_GOODS.F_SMTTypeName,
	    dbo.T_GOODS.F_IsPackage
    FROM    dbo.T_GOODS
            INNER JOIN dbo.T_GOODSTYPE1 ON dbo.T_GOODSTYPE1.F_ID = dbo.T_GOODS.F_GOODSTYPE1ID
            INNER JOIN dbo.T_UNIT ON dbo.T_GOODS.F_UNITID = dbo.T_UNIT.F_ID
            INNER JOIN dbo.T_GOODSTYPE ON dbo.T_GOODS.F_GOODSTYPEID = dbo.T_GOODSTYPE.F_ID;

GO



IF EXISTS(
   SELECT *     FROM syscolumns 
    WHERE id=object_id('T_TICKET') and Name='F_STARTDATE')
BEGIN   
    ALTER TABLE T_TICKET alter COLUMN F_STARTDATE DATETIME
END

GO

IF EXISTS(
   SELECT * 
    FROM syscolumns 
    WHERE id=object_id('T_TICKET') and Name='F_ENDDATE')
BEGIN   
   ALTER TABLE T_TICKET alter COLUMN F_ENDDATE DATETIME
END

GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'V_PackageTicketIssuing')
BEGIN   
  DROP View V_PackageTicketIssuing;
END

GO

--创建视图
CREATE VIEW V_PackageTicketIssuing AS 
SELECT   dbo.T_PackageTicketIssuing.F_ID, dbo.T_PackageTicketIssuing.F_PackageTicketID, 
             dbo.T_PackageTicket.F_GoodsID as F_PackageGoodsID, dbo.T_GOODS.F_NAME As F_PackageName,
                dbo.T_PackageTicketIssuing.F_ExpenseCardID, dbo.T_PackageTicketIssuing.F_HANDBRANDID, 
                 dbo.T_HANDBRAND.F_SHOWID As F_HANDBRANDSHOWID,
                dbo.T_PackageTicketIssuing.F_ClientID, dbo.T_PackageTicketIssuing.F_STATE, 
                dbo.T_PackageTicketIssuing.F_SendDateTime, dbo.T_PackageTicketIssuing.F_UserID, 
                dbo.T_PackageTicketIssuing.F_ValidityDay, dbo.T_PackageTicketIssuing.F_EndDateTime, 
                dbo.T_PackageTicketIssuing.F_UsedDateTime, dbo.T_PackageTicketIssuing.F_SHOPID, 
                dbo.T_PackageTicketIssuing.F_CONSUMEID, dbo.T_PackageTicketIssuing.F_AMOUNT, 
                dbo.T_PackageTicketIssuing.F_GOODSNAME
                
FROM      dbo.T_PackageTicketIssuing INNER JOIN
                dbo.T_PackageTicket ON dbo.T_PackageTicketIssuing.F_PackageTicketID = dbo.T_PackageTicket.F_ID INNER JOIN
                dbo.T_HANDBRAND ON dbo.T_PackageTicketIssuing.F_HandBrandID = dbo.T_HANDBRAND.F_ID INNER JOIN
                dbo.T_GOODS ON dbo.T_PackageTicket.F_GoodsID = dbo.T_GOODS.F_ID


GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'V_TICKET')
BEGIN   
  DROP View V_TICKET;
END

GO
CREATE VIEW [dbo].[V_TICKET] AS
SELECT     T_TICKET.F_SHOPID, T_TICKET.F_SNID, T_TICKET.F_ID, T_TICKET.F_MONEY, T_TICKET.F_STARTDATE, T_TICKET.F_ENDDATE, 
                      T_TICKET.F_STATE, T_TICKET.F_SENDUSER, T_TICKET.F_SENDDATE, T_TICKET.F_SENDSHOPID, T_TICKET.F_CHECKOUTID, 
                      T_TICKET.F_CHECKOUTSHOPID, T_TICKET.F_CHECKOUTTIME, T_TICKET.F_CHECKOUTUSER, T_TICKET.F_CLIENTID, T_TICKET.F_PHONE, 
                      T_TICKET.F_NAME, T_TICKET.F_REMARK, T_TICKET.F_TYPE, ISNULL(T_TICKETTYPE.F_FACTORTYPE, 0) AS F_FACTORTYPE, 
                      T_TICKETTYPE.F_ID AS f_typeid, T_TICKET.F_CONSUMEID, T_TICKET.F_OLDMONEY, T_TICKET.F_RECEIVEID, T_TICKET.F_ClientPromotionID, 
                      T_TICKET.F_GivingCount, T_TICKET.F_OpenID, T_TICKET.F_PackageTicketIssuingID
FROM         T_TICKET LEFT OUTER JOIN
                      T_TICKETTYPE ON T_TICKETTYPE.F_ID = T_TICKET.F_TYPE
;


GO

--修改短信签名的字段长度
alter table T_SMSQueue alter column F_SIGN varchar(100);
GO