IF OBJECT_ID (N'T_ONLINE_CONSUME') IS NULL
BEGIN  --如果不存在该表，则进行创建
CREATE TABLE [T_ONLINE_CONSUME](
	[F_ID] [VARCHAR](20) NOT NULL,
	[F_DATETIME] [DATETIME] NOT NULL,
	[F_CLIENTID] [VARCHAR](20) NOT NULL,
	[F_ACTION] [VARCHAR](100) NOT NULL,
	[F_MONEY] [DECIMAL](9, 2) NOT NULL,
	[F_INTEGRAL] [DECIMAL](9, 2) NOT NULL,
	[F_SHOPID] [VARCHAR](20) NOT NULL,
	[F_CLIENTSHOPID] [VARCHAR](20) NULL,
	[F_ORDERID] [VARCHAR](50) NULL,
	[F_ORDERCODE] [VARCHAR](50) NULL,
	[F_REMARK] [VARCHAR](200) NULL,
 CONSTRAINT [PK_T_ONLINE_CONSUME] 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]

ALTER TABLE [T_ONLINE_CONSUME] ADD  CONSTRAINT [DF_T_ONLINE_CONSUME_F_INTEGRAL]  DEFAULT ((0)) FOR [F_INTEGRAL]

END;

GO


IF EXISTS(
   SELECT * 
    FROM syscolumns 
    WHERE id=object_id('T_INTEGRALCHANGE') and Name='F_TRANSMITGROUP')
BEGIN   
   ALTER TABLE T_INTEGRALCHANGE alter COLUMN F_TRANSMITGROUP varchar(50)
END

GO


if not exists(select * from T_WORKERQUEUEPARAMETER where F_ID = 'IsTechnicianQueryByOnline') 
BEGIN
  INSERT INTO T_WORKERQUEUEPARAMETER (F_ID, F_VALUE, F_SHOPID, F_REMARK, F_ISHIDE, F_TYPE,F_VERSION,F_ADDDATE) VALUES ('IsTechnicianQueryByOnline', '1', NULL, '启用在线查询', 0, '微信(新)','18.0.127','2018-07-17');
END

GO

if not exists(select * from T_WORKERQUEUEPARAMETER where F_ID = 'IsHTTPWorkerQueue') 
BEGIN
  INSERT INTO T_WORKERQUEUEPARAMETER (F_ID, F_VALUE, F_SHOPID, F_REMARK, F_ISHIDE, F_TYPE,F_VERSION,F_ADDDATE) VALUES ('IsHTTPWorkerQueue', '0', NULL, '启用HTTP排钟', 0, '排钟','18.1.127','2018-07-20');
END

GO

if not exists(select * from T_WORKERQUEUEPARAMETER where F_ID = 'HTTPHost') 
BEGIN
  INSERT INTO T_WORKERQUEUEPARAMETER (F_ID, F_VALUE, F_SHOPID, F_REMARK, F_ISHIDE, F_TYPE,F_VERSION,F_ADDDATE) VALUES ('HTTPHost', 'localhost', NULL, 'HTTP服务器IP', 0, '排钟','18.1.127','2018-07-20');
END

GO

if not exists(select * from T_WORKERQUEUEPARAMETER where F_ID = 'HTTPPort') 
BEGIN
  INSERT INTO T_WORKERQUEUEPARAMETER (F_ID, F_VALUE, F_SHOPID, F_REMARK, F_ISHIDE, F_TYPE,F_VERSION,F_ADDDATE) VALUES ('HTTPPort', '8181', NULL, 'HTTP服务端口', 0, '排钟','18.1.127','2018-07-20');
END

GO

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

GO


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

GO


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

GO

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

GO




if not exists(select * from T_SYN_BASETABLE where F_TABLENAME = 'T_TICKETTYPEGOODS')
BEGIN
INSERT INTO [T_SYN_BASETABLE]
           ([F_TABLENAME]
           ,[F_SHOPID]
           ,[F_LASTDATETIME]
           ,[F_PRIORITY]
           ,[F_REMARK]
           ,[F_UPDATEINDEX]
           ,[F_ISFILTERSHOPID])
     VALUES ('T_TICKETTYPEGOODS',null,null,100,null,-1,0)
END

GO




if  exists(select name from sysobjects where name='TICKETTYPEGOODS_ADD')
BEGIN
  DROP TRIGGER [TICKETTYPEGOODS_ADD]
END
GO

Create TRIGGER [TICKETTYPEGOODS_ADD] ON T_TICKETTYPEGOODS
FOR INSERT 
AS
BEGIN
  DECLARE @FNEWID VARCHAR(20);
  SELECT @FNEWID = F_ID FROM inserted;
  EXECUTE UPDATETABLEINDEX 'T_TICKETTYPEGOODS','F_ID', @FNEWID, NULL,'ADD',NULL,NULL;
END


GO

if  exists(select name from sysobjects where name='TICKETTYPEGOODS_DELETE')
BEGIN
  DROP TRIGGER TICKETTYPEGOODS_DELETE
END
GO


Create TRIGGER TICKETTYPEGOODS_DELETE ON T_TICKETTYPEGOODS
FOR DELETE 
AS
BEGIN
  DECLARE @FOLDID VARCHAR(20);
  SELECT @FOLDID = F_ID FROM deleted;
  
  EXECUTE UPDATETABLEINDEX 'T_TICKETTYPEGOODS','F_ID', @FOLDID, NULL,'DELETE',null,NULL;
END


GO

if  exists(select name from sysobjects where name='TICKETTYPEGOODS_UPDATE')
BEGIN
  DROP TRIGGER TICKETTYPEGOODS_UPDATE
END
GO
Create TRIGGER TICKETTYPEGOODS_UPDATE ON T_TICKETTYPEGOODS
FOR UPDATE
AS
BEGIN
  DECLARE @FNEWID VARCHAR(20);
  DECLARE @FOLDID VARCHAR(20);
  SELECT @FNEWID = F_ID FROM inserted;
  SELECT @FOLDID = F_ID FROM deleted;
    
  EXECUTE UPDATETABLEINDEX 'T_TICKETTYPEGOODS','F_ID', @FNEWID,@FOLDID,'UPDATE',null, null;
END

GO


if not exists(select * from T_WORKERQUEUEPARAMETER where F_ID = 'IsUseTicketPacket') 
BEGIN
  INSERT INTO T_WORKERQUEUEPARAMETER (F_ID, F_VALUE, F_SHOPID, F_REMARK, F_ISHIDE, F_TYPE,F_VERSION,F_ADDDATE) VALUES ('IsUseTicketPacket', '0', NULL, '是否启用套票', 0, '系统','18.1.127','2018-07-25');
END


GO



--创建会员套票券次数统计 视图

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

GO
--创建视图
CREATE VIEW V_ClientTicket AS 
select 
F_CLIENTID, 
F_TYPE as F_TYPEID, T_TICKETTYPE.F_NAME as F_TYPENAME, 
Count(F_TYPE) as F_AMOUNT, T_1.F_ENDDATE  
from T_TICKET  as T_1 
 inner join T_TICKETTYPE on (T_1.F_TYPE = T_TICKETTYPE.F_ID)
 inner join T_CLIENT on (T_1.F_CLIENTID = T_CLIENT.F_ID)
where 
T_1.F_STATE = 0 and F_CLIENTID <> '' 
group by F_CLIENTID,  F_TYPE, T_TICKETTYPE.F_NAME, T_1.F_ENDDATE




--套票充值试图
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'V_SupplementByTicket')
BEGIN   
  DROP View V_SupplementByTicket;
END

GO
CREATE VIEW V_SupplementByTicket AS 
SELECT 
T_PackageTicketIssuing.F_SUPPLEMENTID,    
dbo.T_ticket.F_TYPE as F_TYPEID,
T_TICKETTYPE.F_NAME as F_TYPENAME, 
COUNT(1) as F_AMOUNT,
T_ticket.F_ENDDATE
FROM         
dbo.T_ticket
INNER join T_PackageTicketIssuing ON T_ticket.F_PackageTicketIssuingID = T_PackageTicketIssuing.F_ID
inner join T_TICKETTYPE ON T_TICKET.F_TYPE = T_TICKETTYPE.F_ID
group by  T_PackageTicketIssuing.F_SUPPLEMENTID,T_ticket.F_TYPE, T_TICKETTYPE.F_NAME,  T_ticket.F_ENDDATE



--套票券卡类型试图
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'V_CardTypeByTicket')
BEGIN   
  DROP View V_CardTypeByTicket;
END

GO
CREATE VIEW V_CardTypeByTicket AS 
SELECT     
dbo.T_PackageTicket.F_CARDTYPEID,  
dbo.T_PackageTicketType.F_TicketTypeID,
T_TICKETTYPE.F_NAME as F_TYPENAME, 
dbo.T_PackageTicketType.F_Amount,
dbo.T_PackageTicket.F_ValidityDay
FROM         
dbo.T_PackageTicket 
INNER JOIN dbo.T_PackageTicketType ON dbo.T_PackageTicket.F_ID = dbo.T_PackageTicketType.F_PackageTicketID
INNER JOIN dbo.T_TICKETTYPE ON dbo.T_PackageTicketType.F_TicketTypeID = dbo.T_TICKETTYPE.F_ID
where F_CARDTYPEID <> ''


--充值表加入赠送卡字段
IF NOT EXISTS(
   SELECT * 
    FROM syscolumns 
    WHERE id=object_id('T_SUPPLEMENT') and Name='F_PresentCardID')
BEGIN   
   ALTER TABLE T_SUPPLEMENT ADD  F_PresentCardID varchar(20)
END

GO

                      


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

GO

CREATE VIEW   V_SUPPLEMENT
AS
    SELECT  T_SUPPLEMENT.F_ID ,
            T_SUPPLEMENT.F_CLIENTID ,
            T_CLIENT.F_NAME ,
            T_SUPPLEMENT.F_DATETIME ,
            T_SUPPLEMENT.F_MONEY ,
            T_SUPPLEMENT.F_CANCOSTMONEY ,
            T_SUPPLEMENT.F_REMARK ,
            T_SUPPLEMENT.F_USERID ,
            T_SUPPLEMENT.F_SHOPID ,
            T_SUPPLEMENT.F_WORKERID ,
            T_WORKER.F_NAME AS F_WORKERNAME ,
            T_SUPPLEMENT.F_CARDTYPEID ,
            T_CARDTYPE.F_NAME AS F_CARDTYPENAME ,
            T_SUPPLEMENT.F_DEDUCT ,
            T_SUPPLEMENT.F_TRANSMITGROUP ,
            T_SUPPLEMENT.F_TYPE ,
            T_SUPPLEMENT.F_CLIENTCANCOSTMONEY ,
            T_SUPPLEMENT.F_SUPPLEMENTTYPE ,
            T_SUPPLEMENT.F_WORKERDEDUCT ,
            T_SUPPLEMENT.F_FACTMONEY ,
            T_SUPPLEMENT.F_CREDITMONEY ,
            T_CLIENT.F_SHOPID AS F_CLIENTSHOPID ,
            T_SUPPLEMENT.F_CARDMONEY ,
            T_SUPPLEMENT.F_CARDCANCOSTMONEY ,
            T_SUPPLEMENT.F_UNSUPPLEMENTIID ,
            T_SUPPLEMENT.F_INTEGRAL ,
            T_SUPPLEMENT.F_TRADENO ,
            T_CLIENT.F_MOVETEL ,
            T_SUPPLEMENT.F_PRESENTMONEY,
            T_SUPPLEMENT.F_PresentCardID
    FROM    T_SUPPLEMENT
            INNER JOIN T_CLIENT ON T_SUPPLEMENT.F_CLIENTID = T_CLIENT.F_ID
            LEFT OUTER JOIN T_WORKER ON T_SUPPLEMENT.F_CLIENTID = T_WORKER.F_ARCHIVESID
            LEFT OUTER JOIN T_CARDTYPE ON T_SUPPLEMENT.F_CARDTYPEID = T_CARDTYPE.F_ID


GO


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

GO



CREATE VIEW V_SUPPLEMENTPAYMENT(
    "F_CLIENTID",
    "F_NAME",
    "F_DATETIME",
    "f_MONEYSUM",
    "F_CANCOSTMONEY",
    "F_USERID",
    "F_SHOPID",
    "F_WORKERID",
    "F_WORKERNAME",
    "F_CARDTYPEID",
    "F_CARDTYPENAME",
    "F_REMARK",
    "F_DEDUCT",
    "F_TRANSMITGROUP",
    "F_TYPE",
    "F_CLIENTCANCOSTMONEY",
    "F_SUPPLEMENTTYPE",
    "F_WORKERDEDUCT",
    "F_FACTMONEY",
    "F_CREDITMONEY",
    "F_CLIENTSHOPID",
    "F_CARDMONEY",
    "F_CARDCANCOSTMONEY",
    "F_UNSUPPLEMENTIID",
    "F_INTEGRAL",
    "F_SUPPLEMENTID",
    "F_PAYMENTTYPE",
    "F_MONEY",
    "F_TRADENO",
    "F_MOVETEL",
    "F_PRESENTMONEY",
    "F_PresentCardID")
AS
select
  V_SUPPLEMENT.F_CLIENTID,
  V_SUPPLEMENT.F_NAME,
  V_SUPPLEMENT.F_DATETIME,
  V_SUPPLEMENT.F_MONEY AS f_MONEYSUM,
  V_SUPPLEMENT.F_CANCOSTMONEY,
  V_SUPPLEMENT.F_USERID,
  V_SUPPLEMENT.F_SHOPID,
  V_SUPPLEMENT.F_WORKERID,
  V_SUPPLEMENT.F_WORKERNAME,
  V_SUPPLEMENT.F_CARDTYPEID,
  V_SUPPLEMENT.F_CARDTYPENAME,
  V_SUPPLEMENT.F_REMARK,
  V_SUPPLEMENT.F_DEDUCT,
  V_SUPPLEMENT.F_TRANSMITGROUP,
  V_SUPPLEMENT.F_TYPE,
  V_SUPPLEMENT.F_CLIENTCANCOSTMONEY,
  V_SUPPLEMENT.F_SUPPLEMENTTYPE,
  V_SUPPLEMENT.F_WORKERDEDUCT,
  V_SUPPLEMENT.F_FACTMONEY,
  V_SUPPLEMENT.F_CREDITMONEY,
  V_SUPPLEMENT.F_CLIENTSHOPID,
  V_SUPPLEMENT.F_CARDMONEY,
  V_SUPPLEMENT.F_CARDCANCOSTMONEY,
  V_SUPPLEMENT.F_UNSUPPLEMENTIID,
  V_SUPPLEMENT.F_INTEGRAL,
  T_SUPPLEMENTPAYMENT.F_SUPPLEMENTID,
  T_SUPPLEMENTPAYMENT.F_TYPE AS F_PAYMENTTYPE,
  T_SUPPLEMENTPAYMENT.F_MONEY,
  V_SUPPLEMENT.F_TRADENO,
  V_SUPPLEMENT.F_MOVETEL ,
  V_SUPPLEMENT.F_PRESENTMONEY,
  V_SUPPLEMENT.F_PresentCardID
from T_SUPPLEMENTPAYMENT
INNER JOIN V_SUPPLEMENT ON (T_SUPPLEMENTPAYMENT.F_SUPPLEMENTID = V_SUPPLEMENT.F_ID)
; 


GO