--V3.7

if not exists(select * from T_WORKERQUEUEPARAMETER where F_ID = 'DontEmptyRoomIDWhenHangReceive') 
BEGIN
  INSERT INTO T_WORKERQUEUEPARAMETER (F_ID, F_VALUE, F_SHOPID, F_REMARK, F_ISHIDE, F_TYPE,F_VERSION,F_ADDDATE) VALUES ('DontEmptyRoomIDWhenHangReceive', '0', NULL, '挂单时不清空房号', 0, '系统','18.0.124','2018-02-04');
END
GO

if not exists(select * from T_WORKERQUEUEPARAMETER where F_ID = 'EnableHandBrandActivate') 
BEGIN
  INSERT INTO T_WORKERQUEUEPARAMETER (F_ID, F_VALUE, F_SHOPID, F_REMARK, F_ISHIDE, F_TYPE,F_VERSION,F_ADDDATE) VALUES ('EnableHandBrandActivate', '0', NULL, '启用手牌发牌回收机制', 0, '系统','18.0.124','2018-02-04');
END
GO

IF NOT EXISTS(
   SELECT * 
    FROM syscolumns 
    WHERE id=object_id('T_HANDBRAND') and Name='F_ACTIVATED')
BEGIN  
        ALTER TABLE T_HANDBRAND ADD F_ACTIVATED INT NULL DEFAULT 0 
END
Go

if not exists(select * from T_WORKERQUEUEPARAMETER where F_ID = 'EnableInvoiceRoom') 
BEGIN
  INSERT INTO T_WORKERQUEUEPARAMETER (F_ID, F_VALUE, F_SHOPID, F_REMARK, F_ISHIDE, F_TYPE,F_VERSION,F_ADDDATE) VALUES ('EnableInvoiceRoom', '0', NULL, '启用联房机制', 0, '系统','18.0.124','2018-02-10');
END
GO

if not exists(select * from T_WORKERQUEUEPARAMETER where F_ID = 'EnableAutoJoinID') 
BEGIN
  INSERT INTO T_WORKERQUEUEPARAMETER (F_ID, F_VALUE, F_SHOPID, F_REMARK, F_ISHIDE, F_TYPE,F_VERSION,F_ADDDATE) VALUES ('EnableAutoJoinID', '0', NULL, '启用自动团体号机制', 0, '系统','18.0.124','2018-02-10');
END
GO


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

GO

IF EXISTS (select * from sysobjects WHERE NAME='FK_消费外键操作员')
 ALTER TABLE T_CONSUME DROP CONSTRAINT FK_消费外键操作员
 
GO



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

GO

CREATE VIEW V_CONSUMEALL
AS
    SELECT  T_CONSUME.F_ID ,
            T_CHECKOUT.F_ID AS F_CHECKOUTID ,
            T_CHECKOUT.F_DATETIME ,
            T_CONSUME.F_RECEIVEID ,
            T_CONSUME.F_GOODSID ,
            T_GOODS.F_NAME AS F_GOODSNAME ,
            T_GOODS.F_GOODSTYPEID ,
            T_GOODSTYPE.F_NAME AS F_GOODSTYPENAME ,
            T_GOODS.F_GOODSTYPE1ID ,
            T_GOODSTYPE1.F_NAME AS F_GOODSTYPE1NAME ,
            T_GOODS.F_PRICE AS F_GOODSPRICE ,
            T_CONSUME.F_OLDPRICE ,
            T_CONSUME.F_PRICE ,
            T_CONSUME.F_AMOUNT ,
            T_CONSUME.F_RECEIVABLESUM AS F_SUM ,
            T_CONSUME.F_ISPRESENT ,
            T_CONSUME.F_CANREBATE ,
            T_CONSUME.F_REBATE ,
            T_CONSUME.F_REMARK ,
            T_CONSUME.F_USERID AS F_CONSUMEUSERID,
            T_RECEIVE.F_SHOPID ,
            T_CHECKOUT.F_CLIENTID ,
            T_CHECKOUT.F_TYPE ,
            T_CONSUME.F_COST ,
            T_CONSUME.F_STATE ,
            T_CONSUME.F_STARTDATETIME ,
            T_CONSUME.F_ENDDATETIME ,
            T_RECEIVE.F_HANDBRANDID ,
            T_RECEIVE.F_ROOMID ,
            T_RECEIVE.F_TRANSMITGROUP ,
            T_CONSUME.F_ISPROMOTION ,
            T_CONSUME.F_ISTOGO ,
            T_CONSUME.F_ISTIME ,
            T_CONSUME.F_TIMEMEMBERID ,
            T_CONSUME.F_CLIENTREBATE ,
            T_CONSUME.F_CLIENTGOODSREBATE ,
            T_CONSUME.F_PROMOTIONREBATE ,
            T_CONSUME.F_CHECKOUTREBATE ,
            T_CONSUME.F_GATHERINGSUM ,
            T_CONSUME.F_OLDSUM ,
            T_CONSUME.F_RECEIVABLESUM ,
            T_CONSUME.F_WORKERDEDUCT ,
            T_RECEIVE.F_AMOUNT AS F_PERSONTOTALAMOUNT ,
            T_RECEIVE.F_MALEAMOUNT ,
            T_CONSUME.F_ENTERDATETIME ,
            T_CONSUME.F_ROOMAREA,
			T_CHECKOUT.F_USERID AS F_USERID,
            T_CONSUME.F_BACKCONSUMEID AS F_BACKCONSUMEID
    FROM    T_CONSUME
            INNER JOIN T_GOODS ON T_CONSUME.F_GOODSID = T_GOODS.F_ID
                                  AND T_CONSUME.F_GOODSID <> 'StopClock'
            INNER JOIN T_RECEIVE ON T_CONSUME.F_RECEIVEID = T_RECEIVE.F_ID
            LEFT OUTER JOIN T_CHECKOUT ON T_RECEIVE.F_CHECKOUTID = T_CHECKOUT.F_ID
            INNER JOIN T_GOODSTYPE ON T_GOODS.F_GOODSTYPEID = T_GOODSTYPE.F_ID
            INNER JOIN T_GOODSTYPE1 ON T_GOODS.F_GOODSTYPE1ID = T_GOODSTYPE1.F_ID;



GO


if not exists(select * from T_WORKERQUEUEPARAMETER where F_ID = 'IsCancelStopClockToTheTailByAptoticQueue') 
BEGIN
  INSERT INTO T_WORKERQUEUEPARAMETER (F_ID, F_VALUE, F_SHOPID, F_REMARK, F_ISHIDE, F_TYPE,F_VERSION,F_ADDDATE) VALUES ('IsCancelStopClockToTheTailByAptoticQueue', '0', NULL, '固定取消停牌是否打尾牌', 0, '排钟方式','18.0.124','2018-03-04');
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  dbo.T_SUPPLEMENT.F_ID ,
            dbo.T_SUPPLEMENT.F_CLIENTID ,
            dbo.T_CLIENT.F_NAME ,
            dbo.T_SUPPLEMENT.F_DATETIME ,
            dbo.T_SUPPLEMENT.F_MONEY ,
            dbo.T_SUPPLEMENT.F_CANCOSTMONEY ,
            dbo.T_SUPPLEMENT.F_REMARK ,
            dbo.T_SUPPLEMENT.F_USERID ,
            dbo.T_SUPPLEMENT.F_SHOPID ,
            dbo.T_SUPPLEMENT.F_WORKERID ,
            dbo.T_WORKER.F_NAME AS F_WORKERNAME ,
            dbo.T_SUPPLEMENT.F_CARDTYPEID ,
            dbo.T_CARDTYPE.F_NAME AS F_CARDTYPENAME ,
            dbo.T_SUPPLEMENT.F_DEDUCT ,
            dbo.T_SUPPLEMENT.F_TRANSMITGROUP ,
            dbo.T_SUPPLEMENT.F_TYPE ,
            dbo.T_SUPPLEMENT.F_CLIENTCANCOSTMONEY ,
            dbo.T_SUPPLEMENT.F_SUPPLEMENTTYPE ,
            dbo.T_SUPPLEMENT.F_WORKERDEDUCT ,
            dbo.T_SUPPLEMENT.F_FACTMONEY ,
            dbo.T_SUPPLEMENT.F_CREDITMONEY ,
            dbo.T_CLIENT.F_SHOPID AS F_CLIENTSHOPID ,
            dbo.T_SUPPLEMENT.F_CARDMONEY ,
            dbo.T_SUPPLEMENT.F_CARDCANCOSTMONEY ,
            dbo.T_SUPPLEMENT.F_UNSUPPLEMENTIID ,
            dbo.T_SUPPLEMENT.F_INTEGRAL ,
            dbo.T_SUPPLEMENT.F_TRADENO ,
            dbo.T_CLIENT.F_MOVETEL ,
            dbo.T_SUPPLEMENT.F_PRESENTMONEY
    FROM    dbo.T_SUPPLEMENT
            INNER JOIN dbo.T_CLIENT ON dbo.T_SUPPLEMENT.F_CLIENTID = dbo.T_CLIENT.F_ID
            LEFT OUTER JOIN dbo.T_WORKER ON dbo.T_SUPPLEMENT.F_CLIENTID = dbo.T_WORKER.F_ARCHIVESID
            LEFT OUTER JOIN dbo.T_CARDTYPE ON dbo.T_SUPPLEMENT.F_CARDTYPEID = dbo.T_CARDTYPE.F_ID

GO


--hj 更新参数说明，避免误解

if not exists(select * from T_WORKERQUEUEPARAMETER where F_ID = 'IsAllowPlanClockByPriorQueue') 
BEGIN
    UPDATE dbo.T_WORKERQUEUEPARAMETER SET F_REMARK = '高峰期时非预排技师是否参与排钟。（回钟派工时，预排技师是否锁定不被排走）' 
    WHERE F_ID = 'IsAllowPlanClockByPriorQueue';
END
GO


--hj 微信消息推送是否存储到数据库中
if not exists(select * from T_WORKERQUEUEPARAMETER where F_ID = 'IsPushWeChatByTable') 
BEGIN
  INSERT INTO T_WORKERQUEUEPARAMETER (F_ID, F_VALUE, F_SHOPID, F_REMARK, F_ISHIDE, F_TYPE) 
  VALUES ('IsPushWeChatByTable', '0', NULL, '微信消息推送是否记录到数据库再进行消息推送', 0, '微信');
END
GO



--hj 微信推送内容表
IF OBJECT_ID (N'T_WeChatPushMsg') IS NULL
BEGIN  --如果不存在该表，则进行创建
CREATE TABLE [dbo].[T_WeChatPushMsg](
	[GUID] [VARCHAR](100) NOT NULL,
	[ShopId] [VARCHAR](20) NULL,
	[DateTime] [DATETIME] NOT NULL,
	[Msg] [VARCHAR](2500) NOT NULL,
	[State] [INT] NOT NULL,
	[RepeatCount] [INT] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[GUID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 95) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[T_WeChatPushMsg] ADD  DEFAULT ((0)) FOR [State]

ALTER TABLE [dbo].[T_WeChatPushMsg] ADD  DEFAULT ((0)) FOR [RepeatCount]
END
GO