--消费卡模式下加入唯一码自动编号 if not exists(select * from T_AUTOID where F_ID = 'T_UniqueCode') BEGIN INSERT INTO T_AUTOID ([F_ID] ,[F_TYPE] ,[F_VALUE] ,[F_FORMAT] ,[F_REMARK] ,[F_SHOPID]) VALUES ('T_UniqueCode' ,2 ,0 ,'%.6d' ,NULL ,'001'); END GO --hj if not exists(select * from T_WORKERQUEUEPARAMETER where F_ID = 'IsUseYHWMode') BEGIN INSERT INTO T_WORKERQUEUEPARAMETER (F_ID, F_VALUE, F_SHOPID, F_REMARK, F_ISHIDE, F_TYPE,F_VERSION, F_ADDDATE) VALUES ('IsUseYHWMode', '0', NULL, '是否启用预付款模式 0:关闭 1:开启', 0, '其它', '18.0.0', '2016-12-29'); END GO --消费卡 IF OBJECT_ID (N'T_ExpenseCard') IS NULL BEGIN --如果不存在该表，则进行创建 CREATE TABLE [dbo].[T_ExpenseCard]( [F_ID] [VARCHAR](20) NOT NULL, [F_CARDID] [VARCHAR](20) NOT NULL, [F_INTERNALCARDID] [VARCHAR](20) NULL, [F_MOVETEL] [VARCHAR](50) NULL, [F_NAME] [VARCHAR](20) NULL, [F_TYPE] [VARCHAR](10) NOT NULL, [F_CANCOSTMONEY] [DECIMAL](15, 2) NOT NULL, [F_CURRENTUSEDMONEY] [DECIMAL](15, 2) NOT NULL, [F_SUPPLEMENTTOTALMONEY] [DECIMAL](15, 2) NOT NULL, [F_STATE] [VARCHAR](10) NOT NULL, [F_REMARK] [VARCHAR](250) NULL, [F_SHOPID] [VARCHAR](10) NULL, [F_IsAvaliable] [INT] NOT NULL, [F_Version] [INT] NOT NULL, [F_UniqueCode] [VARCHAR](50) NULL, [F_MoneyIn] [DECIMAL](15, 2) NULL, [F_MoneyOut] [DECIMAL](15, 2) NULL, CONSTRAINT [PK_T_ExpenseCard] 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, FILLFACTOR = 95) ON [PRIMARY] ) ON [PRIMARY] ALTER TABLE [dbo].[T_ExpenseCard] ADD CONSTRAINT [DF_T_ExpenseCard_F_IsAvaliable] DEFAULT ((0)) FOR [F_IsAvaliable] ALTER TABLE [dbo].[T_ExpenseCard] ADD CONSTRAINT [DF_T_ExpenseCard_F_Version] DEFAULT ((0)) FOR [F_Version] ALTER TABLE [dbo].[T_ExpenseCard] WITH CHECK ADD CONSTRAINT [FK_T_ExpenseCard_INTRODUCERID] FOREIGN KEY([F_ID]) REFERENCES [dbo].[T_ExpenseCard] ([F_ID]) ALTER TABLE [dbo].[T_ExpenseCard] CHECK CONSTRAINT [FK_T_ExpenseCard_INTRODUCERID] END GO --消费卡充值 IF OBJECT_ID (N'T_ExpenseSupplement') IS NULL BEGIN --如果不存在该表，则进行创建 CREATE TABLE [dbo].[T_ExpenseSupplement]( [F_ID] [varchar](20) NOT NULL, [F_ExpenseCardID] [varchar](20) NOT NULL, [F_Money] [decimal](15, 2) NOT NULL, [F_PAYTYPE] [varchar](250) NULL, [F_DATETIME] [datetime] NULL, [F_USERID] [varchar](20) NOT NULL, [F_SHOPID] [varchar](10) NULL, [F_TYPE] [varchar](10) NOT NULL, [F_REMARK] [varchar](250) NULL, CONSTRAINT [PK_T_ExpenseSupplement] 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, FILLFACTOR = 95) ON [PRIMARY] ) ON [PRIMARY] END GO -- IF OBJECT_ID (N'T_ExpenseSupplementPayment') IS NULL BEGIN --如果不存在该表，则进行创建 CREATE TABLE [dbo].[T_ExpenseSupplementPayment]( [F_ID] [varchar](20) NOT NULL, [F_TYPE] [varchar](10) NOT NULL, [F_MONEY] [decimal](15, 2) NOT NULL, [F_ExpenseSupplementID] [varchar](20) NOT NULL, CONSTRAINT [PK_T_ExpenseSupplementPayment] 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, FILLFACTOR = 95) ON [PRIMARY] ) ON [PRIMARY] END GO --主从绑卡 IF OBJECT_ID (N'T_MasterSlaveRelation') IS NULL BEGIN --如果不存在该表，则进行创建 CREATE TABLE [dbo].[T_MasterSlaveRelation]( [F_ID] [VARCHAR](20) NOT NULL, [F_MasterCardID] [VARCHAR](20) NOT NULL, [F_SlaveCardID] [VARCHAR](20) NOT NULL, [F_LimitMoney] [DECIMAL](15, 2) NULL, [F_LimitDateTime] [DATETIME] NULL, [F_SHOPID] [VARCHAR](10) NULL, [F_DateTime] [DATETIME] NULL, [F_UserID] [VARCHAR](20) NULL ) ON [PRIMARY] END GO --日志 IF OBJECT_ID (N'T_ExpenseCardLog') IS NULL BEGIN --如果不存在该表，则进行创建 CREATE TABLE [dbo].[T_ExpenseCardLog]( [F_ID] [varchar](20) NOT NULL, [F_UserID] [varchar](20) NOT NULL, [F_DateTime] [datetime] NOT NULL, [F_UniqueCode] [varchar](50) NULL, [F_ExpenseCardID] [varchar](20) NULL, [F_Content] [varchar](500) NULL, [F_Remark] [varchar](250) NULL, [F_SHOPID] [varchar](10) NULL ) ON [PRIMARY] END GO --日志 IF OBJECT_ID (N'T_ExpenseCardMoneyChange') IS NULL BEGIN --如果不存在该表，则进行创建 CREATE TABLE [dbo].[T_ExpenseCardMoneyChange]( [F_ID] [VARCHAR](20) NOT NULL, [F_OutCardID] [VARCHAR](20) NOT NULL, [F_InCardID] [VARCHAR](20) NOT NULL, [F_DateTime] [DATETIME] NULL, [F_UserID] [VARCHAR](20) NULL, [F_Money] [DECIMAL](15, 2) NOT NULL, [F_OutCanCostMoney] [DECIMAL](15, 2) NOT NULL, [F_InCanCostMoney] [DECIMAL](15, 2) NOT NULL, [F_ShopID] [VARCHAR](10) NULL, [F_Remark] [VARCHAR](250) NULL ) ON [PRIMARY] END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_CHECKOUT') and Name='F_ExpenseCardID') BEGIN ALTER TABLE T_CHECKOUT ADD F_ExpenseCardID varchar(20) END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_CHECKOUT') and Name='F_ExpenseCardCode') BEGIN ALTER TABLE T_CHECKOUT ADD F_ExpenseCardCode varchar(20) END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_RECEIVE') and Name='F_ExpenseCardID') BEGIN ALTER TABLE T_RECEIVE ADD F_ExpenseCardID varchar(20) END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_RECEIVE') and Name='F_ExpenseCardCode') BEGIN ALTER TABLE T_RECEIVE ADD F_ExpenseCardCode varchar(20) END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_CONSUME') and Name='F_ExpenseCardID') BEGIN ALTER TABLE T_CONSUME ADD F_ExpenseCardID varchar(20) END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_CONSUME') and Name='F_ExpenseCardCode') BEGIN ALTER TABLE T_CONSUME ADD F_ExpenseCardCode varchar(20) END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_CONSUME') and Name='F_ROOMAREA') BEGIN ALTER TABLE T_CONSUME ADD F_ROOMAREA varchar(20) END GO IF NOT EXISTS( SELECT * FROM T_CHECKOUTTYPE WHERE F_ID = 'W22') BEGIN INSERT T_CHECKOUTTYPE ( F_ID , F_NAME , F_OrderID , F_IsStop , F_IsSupplent , F_IsCheckout , F_IsNeedAuthorize ) VALUES ( 'W22' , -- F_ID - varchar(10) '预付款' , -- F_NAME - varchar(20) '9999' , -- F_OrderID - varchar(10) 0 , -- F_IsStop - smallint 1 , -- F_IsSupplent - smallint 1 , -- F_IsCheckout - smallint 0 ) END GO if exists(select name from sysobjects where name='V_CONSUMENOTCHECKOUT') DROP VIEW [V_CONSUMENOTCHECKOUT] GO CREATE VIEW [dbo].[V_CONSUMENOTCHECKOUT] AS SELECT dbo.T_CONSUME.F_ID, dbo.T_CONSUME.F_RECEIVEID, dbo.T_CONSUME.F_GOODSID, dbo.T_CONSUME.F_PRICE, dbo.T_CONSUME.F_AMOUNT, dbo.T_CONSUME.F_ISPRESENT, dbo.T_CONSUME.F_CANREBATE, dbo.T_CONSUME.F_REBATE, dbo.T_CONSUMEWORKER.F_HEADSHIPID, dbo.T_HEADSHIP.F_NAME AS F_HEADSHIPNAME, dbo.T_CONSUMEWORKER.F_WORKERID, dbo.T_CONSUMEWORKER.F_ISNAMED, dbo.T_CONSUME.F_REMARK, dbo.T_CONSUME.F_USERID, dbo.T_CONSUME.F_COST, dbo.T_CONSUME.F_STATE, dbo.T_GOODS.F_NAME, dbo.T_GOODS.F_GOODSTYPEID, dbo.T_GOODSTYPE.F_NAME AS F_GOODSTYPENAME, dbo.T_GOODS.F_GOODSTYPE1ID, dbo.T_GOODSTYPE1.F_NAME AS F_GOODSTYPE1NAME, dbo.T_GOODSTYPE1.F_STATE AS F_GOODSTYPE1STATE, dbo.T_GOODSTYPE1.F_ISSTORAGE, dbo.T_RECEIVE.F_DATETIME, dbo.T_RECEIVE.F_CHECKOUTID, dbo.T_RECEIVE.F_SHOPID, dbo.T_RECEIVE.F_HANDBRANDID, dbo.T_HANDBRAND.F_SHOWID AS F_HANDBRANDSHOWID, dbo.T_RECEIVE.F_ROOMID, dbo.T_ROOM.F_SHOWID AS F_ROOMSHOWID, dbo.T_RECEIVE.F_JOINID, dbo.T_RECEIVE.F_AMOUNT AS F_PERSONTOTALAMOUNT, dbo.T_RECEIVE.F_MALEAMOUNT, dbo.T_CONSUME.F_STARTDATETIME, dbo.T_CONSUME.F_ENDDATETIME, dbo.T_GOODS.F_STARTTIMES, dbo.T_GOODS.F_STARTTIMESPRICE, dbo.T_GOODS.F_TIMES, dbo.T_GOODS.F_TIMESPRICE, dbo.T_CONSUMEWORKER.F_ID AS F_CONSUMEWORKERID, dbo.T_CONSUME.F_ENTERDATETIME, (CASE WHEN T_RECEIVE.F_MEMORANDUM IS NOT NULL THEN T_RECEIVE.F_MEMORANDUM ELSE dbo.T_RECEIVE.F_REMARK END) AS F_RECEIVEREMARK, dbo.T_CONSUMEWORKER.F_GOODSDEDUCTAUTOID, dbo.T_CONSUMEWORKER.F_OUTDATETIME, dbo.T_CONSUMEWORKER.F_BACKDATETIME, dbo.T_CONSUME.F_MINISTERID, dbo.T_GOODS.F_ISSMALL, dbo.T_CONSUMEWORKER.F_ISADDCLOCK, dbo.T_CONSUME.F_DELAY, dbo.T_CONSUME.F_DELAYID, dbo.T_RECEIVE.F_PRIORCHECKOUTID, dbo.T_CONSUME.F_ISPRIOR, dbo.T_CONSUMEWORKER.F_WALKTHROUGHINDEX, dbo.T_CONSUMEWORKER.F_ISSTOPWALKTHROUGH, dbo.T_CONSUMEWORKER.F_ENTERDATETIME AS F_WORKERENTERDATETIME, dbo.T_GOODS.F_HEADSHIPTRAIT AS F_GOODSHEADSHIPTRAIT, dbo.T_RECEIVE.F_ISSTOPAUTOTEA, dbo.T_WORKER.F_ID AS F_SHOWWORKERID, t1.F_ID AS F_SHOWMINISTERID, dbo.T_GOODS.F_ISEXTERNALSOUND, T_CONSUME.F_SATISFYCOMSUMEWORKERID, dbo.T_RECEIVE.F_CLIENTID, dbo.T_CONSUME.F_SEATNUMBER, dbo.T_CONSUME.F_HEADSHIPLEVEL, dbo.T_CONSUME.F_GOODSPLUSPARENTID, dbo.T_CONSUME.F_ExpenseCardID, dbo.T_CONSUME.F_ExpenseCardCode FROM dbo.T_RECEIVE INNER JOIN dbo.T_CONSUME ON dbo.T_RECEIVE.F_ID = dbo.T_CONSUME.F_RECEIVEID AND dbo.T_RECEIVE.F_CHECKOUTID IS NULL LEFT OUTER JOIN dbo.T_CONSUMEWORKER ON dbo.T_CONSUME.F_ID = dbo.T_CONSUMEWORKER.F_CONSUMEID LEFT OUTER JOIN dbo.T_HEADSHIP ON dbo.T_CONSUMEWORKER.F_HEADSHIPID = dbo.T_HEADSHIP.F_ID LEFT OUTER JOIN dbo.T_GOODS ON dbo.T_CONSUME.F_GOODSID = dbo.T_GOODS.F_ID LEFT OUTER JOIN dbo.T_GOODSTYPE ON dbo.T_GOODS.F_GOODSTYPEID = dbo.T_GOODSTYPE.F_ID LEFT OUTER JOIN dbo.T_GOODSTYPE1 ON dbo.T_GOODS.F_GOODSTYPE1ID = dbo.T_GOODSTYPE1.F_ID LEFT OUTER JOIN dbo.T_ROOM ON dbo.T_RECEIVE.F_ROOMID = dbo.T_ROOM.F_ID LEFT OUTER JOIN dbo.T_HANDBRAND ON dbo.T_RECEIVE.F_HANDBRANDID = dbo.T_HANDBRAND.F_ID LEFT OUTER JOIN dbo.T_WORKER ON dbo.T_CONSUMEWORKER.F_WORKERID = dbo.T_WORKER.F_ARCHIVESID LEFT OUTER JOIN dbo.T_WORKER AS t1 ON dbo.T_CONSUME.F_MINISTERID = t1.F_ARCHIVESID GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_HANDBRAND') and Name='F_ISHIDE') BEGIN ALTER TABLE T_HANDBRAND ADD F_ISHIDE INTEGER NOT NULL DEFAULT 0 END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_HANDBRAND') and Name='F_BindExpenseCardID') BEGIN ALTER TABLE T_HANDBRAND ADD F_BindExpenseCardID VARCHAR(20) END GO IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'V_ExpenseCard') BEGIN DROP View V_ExpenseCard; END GO --创建视图 CREATE VIEW [dbo].[V_ExpenseCard] AS SELECT F_CARDID, F_INTERNALCARDID, F_MOVETEL, F_NAME, F_TYPE, F_STATE, F_CANCOSTMONEY, F_CURRENTUSEDMONEY, F_SUPPLEMENTTOTALMONEY, F_REMARK, T_ExpenseCard.F_SHOPID, F_UniqueCode, F_IsAvaliable, (CASE WHEN F_MasterCardID IS NULL THEN F_CARDID ELSE F_MasterCardID END ) AS F_MasterCardID, F_UserID, F_LimitDateTime, F_LimitMoney, F_DateTime, F_MONEYIN, F_MONEYOUT FROM T_ExpenseCard LEFT JOIN T_MasterSlaveRelation ON (T_ExpenseCard.F_CARDID = T_MasterSlaveRelation.F_SlaveCardID) GO --房间区域 IF OBJECT_ID (N'T_ROOMAREA') IS NULL BEGIN --如果不存在该表，则进行创建 CREATE TABLE [dbo].[T_ROOMAREA]( [F_ID] [VARCHAR](10) NOT NULL, [F_NAME] [VARCHAR](50) NULL, [F_SHOPID] [VARCHAR](10) NULL, CONSTRAINT [PK_ROOMAREA_FID] 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] END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_ROOM') and Name='F_ROOMAREANAME') BEGIN ALTER TABLE T_ROOM ADD F_ROOMAREANAME varchar(50) NULL END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_CARDTYPE') and Name='F_IsSpecialCard') BEGIN ALTER TABLE T_CARDTYPE ADD F_IsSpecialCard INTEGER DEFAULT 0 END IF OBJECT_ID (N'T_ROOMSEAT') IS NULL BEGIN --如果不存在该表，则进行创建 CREATE TABLE [dbo].[T_ROOMSEAT]( [F_ID] [VARCHAR](10) NOT NULL, [F_SHOPID] [VARCHAR](10) NULL, [F_SEATID] [VARCHAR](10) NOT NULL, [F_ROOMID] [VARCHAR](10) NULL, [F_REMARK] [VARCHAR](100) NULL, [F_SEATSHOWID] [VARCHAR](20) NOT NULL, CONSTRAINT [PK_T_ROOMSEAT] 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, FILLFACTOR = 95) ON [PRIMARY] ) ON [PRIMARY] END GO if exists(select name from sysobjects where name='V_CONSUME') DROP VIEW [V_CONSUME] GO CREATE VIEW [dbo].[V_CONSUME] AS SELECT dbo.T_CONSUME.F_ID, dbo.T_CHECKOUT.F_ID AS F_CHECKOUTID, dbo.T_CHECKOUT.F_DATETIME, dbo.T_CHECKOUT.F_USERID AS F_CHECKOUTUSERID, dbo.T_CONSUME.F_RECEIVEID, dbo.T_CONSUME.F_GOODSID, dbo.T_GOODS.F_NAME AS F_GOODSNAME, dbo.T_GOODS.F_GOODSTYPEID, dbo.T_GOODSTYPE.F_NAME AS F_GOODSTYPENAME, dbo.T_GOODS.F_GOODSTYPE1ID, dbo.T_GOODSTYPE1.F_NAME AS F_GOODSTYPE1NAME, dbo.T_GOODS.F_PRICE AS F_GOODSPRICE, dbo.T_CONSUME.F_OLDPRICE, dbo.T_CONSUME.F_PRICE, dbo.T_CONSUME.F_AMOUNT, dbo.T_CONSUME.F_RECEIVABLESUM AS F_SUM, dbo.T_CONSUME.F_ISPRESENT, dbo.T_CONSUME.F_CANREBATE, dbo.T_CONSUME.F_REBATE, dbo.T_CONSUME.F_REMARK, dbo.T_CONSUME.F_USERID, dbo.T_CHECKOUT.F_SHOPID, dbo.T_CHECKOUT.F_CLIENTID, dbo.T_CHECKOUT.F_TYPE, dbo.T_CONSUME.F_COST, dbo.T_CONSUME.F_STATE, dbo.T_CONSUME.F_STARTDATETIME, dbo.T_CONSUME.F_ENDDATETIME, dbo.T_RECEIVE.F_HANDBRANDID, dbo.T_RECEIVE.F_ROOMID, dbo.T_RECEIVE.F_TRANSMITGROUP, dbo.T_CONSUME.F_ISPROMOTION, dbo.T_CONSUME.F_ISTOGO, dbo.T_CONSUME.F_ISTIME, dbo.T_CONSUME.F_TIMEMEMBERID, dbo.T_CONSUME.F_CLIENTREBATE, dbo.T_CONSUME.F_CLIENTGOODSREBATE, dbo.T_CONSUME.F_PROMOTIONREBATE, dbo.T_CONSUME.F_CHECKOUTREBATE, dbo.T_CONSUME.F_GATHERINGSUM, dbo.T_CONSUME.F_OLDSUM, dbo.T_CONSUME.F_RECEIVABLESUM, dbo.T_CONSUME.F_WORKERDEDUCT, dbo.T_RECEIVE.F_AMOUNT AS F_PERSONTOTALAMOUNT, dbo.T_RECEIVE.F_MALEAMOUNT, dbo.T_CONSUME.F_ENTERDATETIME, dbo.T_CONSUME.F_TICKETMONEY, dbo.T_CONSUME.F_SEATNUMBER, dbo.T_CONSUME.F_ExpenseCardID, dbo.T_CONSUME.F_ExpenseCardCode FROM dbo.T_CONSUME INNER JOIN dbo.T_RECEIVE ON dbo.T_CONSUME.F_RECEIVEID = dbo.T_RECEIVE.F_ID INNER JOIN dbo.T_CHECKOUT ON dbo.T_RECEIVE.F_CHECKOUTID = dbo.T_CHECKOUT.F_ID left JOIN dbo.T_GOODS ON dbo.T_CONSUME.F_GOODSID = dbo.T_GOODS.F_ID left JOIN dbo.T_GOODSTYPE ON dbo.T_GOODS.F_GOODSTYPEID = dbo.T_GOODSTYPE.F_ID left JOIN dbo.T_GOODSTYPE1 ON dbo.T_GOODS.F_GOODSTYPE1ID = dbo.T_GOODSTYPE1.F_ID; GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_SMARTLIST') and Name='F_ROOMTYPEID') BEGIN ALTER TABLE T_SMARTLIST ADD F_ROOMTYPEID VARCHAR(10) END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_ROOM') and Name='F_ISSEAT') BEGIN ALTER TABLE T_ROOM ADD F_ISSEAT INTEGER NULL DEFAULT 0 END GO if exists(select name from sysobjects where name='V_ROOM') DROP VIEW V_ROOM GO CREATE VIEW [V_ROOM] AS SELECT t_room.f_id, t_room.f_showid, t_room.f_roomtypeid, t_roomtype.f_name f_roomtype, t_room.f_state, t_room.f_amount, t_room.f_checkinamount, t_room.f_remark, t_room.f_shopid, t_floor.f_autoid AS F_FLOORAUTOID, t_floor.f_name AS F_FLOORNAME, t_room.F_ISCHARGE, t_room.f_goodsid, T_GOODS.f_name AS F_GOODSNAME, t_room.F_ISTAKE, t_room.f_workerid, t_room.f_destinedatetime, t_room.F_HOMOPHONY, t_room.F_ISLEAST, t_room.F_LEAST, t_room.F_ISAUDIOMUTING, t_room.F_ISAUTOTEAREMIND, t_room.F_BangWeiRoomCode, t_room.F_ISSEAT FROM t_room INNER JOIN t_roomtype ON (t_room.f_roomtypeid = t_roomtype.f_id) LEFT JOIN t_floor ON (t_floor.f_autoid = t_room.f_floorautoid) LEFT JOIN T_GOODS ON (T_GOODS.f_id = T_ROOM.f_goodsid) ; GO IF OBJECT_ID (N'T_INVOICE') IS NULL BEGIN --如果不存在该表，则进行创建 CREATE TABLE [dbo].[T_INVOICE]( [F_ID] [VARCHAR](20) NOT NULL, [F_INVOICEID] [VARCHAR](20) NULL, [F_INVOICEMONEY] [DECIMAL](9, 2) NULL, [F_INVOICETITLE] [VARCHAR](50) NULL, [F_USERNAME] [VARCHAR](20) NULL, [F_CREATEDATETIME] [DATETIME] NULL, [F_PAYMENT] [DECIMAL](9, 2) NULL, [F_ISVAT] [SMALLINT] NULL, [F_ISOBSOLETE] [SMALLINT] NULL, [F_REMARK] [VARCHAR](500) NULL, [F_CHECKOUTID] [VARCHAR](20) NULL, [F_SUPPLEMENTID] [VARCHAR](20) NULL, [F_RECEIVEID] [VARCHAR](20) NULL, [F_SHOPID] [VARCHAR](10) NULL, CONSTRAINT [PK_T_INVOICE] 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] END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_INTEGRALCHANGE') and Name='F_ISSTORAGE') BEGIN ALTER TABLE T_INTEGRALCHANGE ADD F_ISSTORAGE SMALLINT END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_INTEGRALCHANGE_H') and Name='F_ISSTORAGE') BEGIN ALTER TABLE T_INTEGRALCHANGE_H ADD F_ISSTORAGE SMALLINT END GO if exists(select name from sysobjects where name='V_INTEGRALCHANGE') DROP VIEW V_INTEGRALCHANGE GO CREATE VIEW V_INTEGRALCHANGE AS SELECT dbo.T_INTEGRALCHANGE.F_AUTOID, dbo.T_INTEGRALCHANGE.F_DATE, dbo.T_INTEGRALCHANGE.F_CLIENTID, dbo.T_INTEGRALCHANGE.F_INTEGRAL, dbo.T_INTEGRALCHANGE.F_REMARK, dbo.T_INTEGRALCHANGE.F_CHANGEMODE, dbo.T_INTEGRALCHANGE.F_CHANGEMONEY, dbo.T_CLIENT.F_NAME AS F_CLIENTNAME, dbo.T_CLIENT.F_INTEGRAL AS F_CLIENTINTEGRAL, dbo.T_INTEGRALCHANGE.F_USERNAME, dbo.T_INTEGRALCHANGE.F_SHOPID, dbo.T_INTEGRALCHANGE.F_TRANSMITGROUP, dbo.T_INTEGRALCHANGE.F_LASTDATE, dbo.T_INTEGRALCHANGE.F_ISSTORAGE FROM dbo.T_CLIENT INNER JOIN dbo.T_INTEGRALCHANGE ON dbo.T_CLIENT.F_ID = dbo.T_INTEGRALCHANGE.F_CLIENTID GO if exists(select name from sysobjects where name='V_EXPENSESUPPLEMENTPAYMENT') DROP VIEW [V_EXPENSESUPPLEMENTPAYMENT] GO CREATE VIEW [dbo].[V_EXPENSESUPPLEMENTPAYMENT] AS select T_ExpenseSupplement.F_ExpenseCardID, T_ExpenseSupplement.F_DATETIME, T_ExpenseSupplement.F_MONEY AS f_MONEYSUM, T_ExpenseSupplement.F_USERID, T_ExpenseSupplement.F_SHOPID, T_ExpenseSupplement.F_REMARK, T_ExpenseSupplement.F_TYPE, T_ExpenseSupplement.F_PAYTYPE As F_SUPPLEMENTTYPE, T_ExpenseSupplementPayment.F_ExpenseSupplementID, T_ExpenseSupplementPayment.F_TYPE AS F_PAYMENTTYPE, T_ExpenseSupplementPayment.F_MONEY from T_ExpenseSupplementPayment INNER JOIN T_ExpenseSupplement ON (T_ExpenseSupplement.F_ID = T_ExpenseSupplementPayment.F_ExpenseSupplementID) GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_CARDCHANGELOG') and Name='F_OldBeforeIntegral') BEGIN ALTER TABLE T_CARDCHANGELOG ADD F_OldBeforeIntegral DECIMAL(15, 2) END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_CARDCHANGELOG') and Name='F_OldAfterIntegral') BEGIN ALTER TABLE T_CARDCHANGELOG ADD F_OldAfterIntegral DECIMAL(15, 2) END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_CARDCHANGELOG') and Name='F_NewBeforeIntegral') BEGIN ALTER TABLE T_CARDCHANGELOG ADD F_NewBeforeIntegral DECIMAL(15, 2) END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_CARDCHANGELOG') and Name='F_NewAfterIntegral') BEGIN ALTER TABLE T_CARDCHANGELOG ADD F_NewAfterIntegral DECIMAL(15, 2) END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_CARDCHANGELOG') and Name='F_Integral') BEGIN ALTER TABLE T_CARDCHANGELOG ADD F_Integral DECIMAL(15, 2) END GO IF OBJECT_ID (N'T_TASTE') IS NULL BEGIN --如果不存在该表，则进行创建 CREATE TABLE [dbo].[T_TASTE]( [F_ID] [VARCHAR](10) NOT NULL, [F_NAME] [VARCHAR](50) NULL, [F_SHOPID] [VARCHAR](10) NULL, CONSTRAINT [PK_T_TASTE] 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] END GO IF OBJECT_ID (N'T_GOODSTASTE') IS NULL BEGIN --如果不存在该表，则进行创建 CREATE TABLE [dbo].[T_GOODSTASTE]( [F_ID] [varchar](10) NOT NULL, [F_GOODSID] [varchar](20) NULL, [F_TASTE] [varchar](500) NULL, [F_SHOPID] [varchar](10) NULL, CONSTRAINT [PK_T_GOODSTASTE] 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] END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_CHECKOUT_H') and Name='F_ExpenseCardID') BEGIN ALTER TABLE T_CHECKOUT_H ADD F_ExpenseCardID varchar(20) END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_CHECKOUT_H') and Name='F_ExpenseCardCode') BEGIN ALTER TABLE T_CHECKOUT_H ADD F_ExpenseCardCode varchar(20) END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_RECEIVE_H') and Name='F_ExpenseCardID') BEGIN ALTER TABLE T_RECEIVE_H ADD F_ExpenseCardID varchar(20) END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_RECEIVE_H') and Name='F_ExpenseCardCode') BEGIN ALTER TABLE T_RECEIVE_H ADD F_ExpenseCardCode varchar(20) END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_CONSUME_H') and Name='F_ExpenseCardID') BEGIN ALTER TABLE T_CONSUME_H ADD F_ExpenseCardID varchar(20) END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_CONSUME_H') and Name='F_ExpenseCardCode') BEGIN ALTER TABLE T_CONSUME_H ADD F_ExpenseCardCode varchar(20) END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_CONSUME_H') and Name='F_ROOMAREA') BEGIN ALTER TABLE T_CONSUME_H ADD F_ROOMAREA varchar(20) END GO if exists(select name from sysobjects where name='V_CONSUME') DROP VIEW [V_CONSUME] GO CREATE VIEW [dbo].[V_CONSUME] AS SELECT dbo.T_CONSUME.F_ID, dbo.T_CHECKOUT.F_ID AS F_CHECKOUTID, dbo.T_CHECKOUT.F_DATETIME, dbo.T_CHECKOUT.F_USERID AS F_CHECKOUTUSERID, dbo.T_CONSUME.F_RECEIVEID, dbo.T_CONSUME.F_GOODSID, dbo.T_GOODS.F_NAME AS F_GOODSNAME, dbo.T_GOODS.F_GOODSTYPEID, dbo.T_GOODSTYPE.F_NAME AS F_GOODSTYPENAME, dbo.T_GOODS.F_GOODSTYPE1ID, dbo.T_GOODSTYPE1.F_NAME AS F_GOODSTYPE1NAME, dbo.T_GOODS.F_PRICE AS F_GOODSPRICE, dbo.T_CONSUME.F_OLDPRICE, dbo.T_CONSUME.F_PRICE, dbo.T_CONSUME.F_AMOUNT, dbo.T_CONSUME.F_RECEIVABLESUM AS F_SUM, dbo.T_CONSUME.F_ISPRESENT, dbo.T_CONSUME.F_CANREBATE, dbo.T_CONSUME.F_REBATE, dbo.T_CONSUME.F_REMARK, dbo.T_CONSUME.F_USERID, dbo.T_CHECKOUT.F_SHOPID, dbo.T_CHECKOUT.F_CLIENTID, dbo.T_CHECKOUT.F_TYPE, dbo.T_CONSUME.F_COST, dbo.T_CONSUME.F_STATE, dbo.T_CONSUME.F_STARTDATETIME, dbo.T_CONSUME.F_ENDDATETIME, dbo.T_RECEIVE.F_HANDBRANDID, dbo.T_RECEIVE.F_ROOMID, dbo.T_RECEIVE.F_TRANSMITGROUP, dbo.T_CONSUME.F_ISPROMOTION, dbo.T_CONSUME.F_ISTOGO, dbo.T_CONSUME.F_ISTIME, dbo.T_CONSUME.F_TIMEMEMBERID, dbo.T_CONSUME.F_CLIENTREBATE, dbo.T_CONSUME.F_CLIENTGOODSREBATE, dbo.T_CONSUME.F_PROMOTIONREBATE, dbo.T_CONSUME.F_CHECKOUTREBATE, dbo.T_CONSUME.F_GATHERINGSUM, dbo.T_CONSUME.F_OLDSUM, dbo.T_CONSUME.F_RECEIVABLESUM, dbo.T_CONSUME.F_WORKERDEDUCT, dbo.T_RECEIVE.F_AMOUNT AS F_PERSONTOTALAMOUNT, dbo.T_RECEIVE.F_MALEAMOUNT, dbo.T_CONSUME.F_ENTERDATETIME, dbo.T_CONSUME.F_TICKETMONEY, dbo.T_CONSUME.F_SEATNUMBER, dbo.T_CONSUME.F_ExpenseCardID, dbo.T_CONSUME.F_ExpenseCardCode, dbo.T_CONSUME.F_ROOMAREA FROM dbo.T_CONSUME INNER JOIN dbo.T_RECEIVE ON dbo.T_CONSUME.F_RECEIVEID = dbo.T_RECEIVE.F_ID INNER JOIN dbo.T_CHECKOUT ON dbo.T_RECEIVE.F_CHECKOUTID = dbo.T_CHECKOUT.F_ID left JOIN dbo.T_GOODS ON dbo.T_CONSUME.F_GOODSID = dbo.T_GOODS.F_ID left JOIN dbo.T_GOODSTYPE ON dbo.T_GOODS.F_GOODSTYPEID = dbo.T_GOODSTYPE.F_ID left JOIN dbo.T_GOODSTYPE1 ON dbo.T_GOODS.F_GOODSTYPE1ID = dbo.T_GOODSTYPE1.F_ID; GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_GRATISTCONSUME') and Name='F_ROOMAREA') BEGIN ALTER TABLE T_GRATISTCONSUME ADD F_ROOMAREA varchar(20) END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_PRINTERSETUP') and Name='F_ROOMAREA') BEGIN ALTER TABLE T_PRINTERSETUP ADD F_ROOMAREA varchar(20) END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_PRINTERSETUP') and Name='F_ROOMAREA') BEGIN ALTER TABLE T_PRINTERSETUP ADD F_ROOMAREA varchar(20) END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_CARDCHANGELOG_H') and Name='F_OldBeforeIntegral') BEGIN ALTER TABLE T_CARDCHANGELOG_H ADD F_OldBeforeIntegral DECIMAL(15, 2) END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_CARDCHANGELOG_H') and Name='F_OldAfterIntegral') BEGIN ALTER TABLE T_CARDCHANGELOG_H ADD F_OldAfterIntegral DECIMAL(15, 2) END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_CARDCHANGELOG_H') and Name='F_NewBeforeIntegral') BEGIN ALTER TABLE T_CARDCHANGELOG_H ADD F_NewBeforeIntegral DECIMAL(15, 2) END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_CARDCHANGELOG_H') and Name='F_NewAfterIntegral') BEGIN ALTER TABLE T_CARDCHANGELOG_H ADD F_NewAfterIntegral DECIMAL(15, 2) END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_CARDCHANGELOG_H') and Name='F_Integral') BEGIN ALTER TABLE T_CARDCHANGELOG_H ADD F_Integral DECIMAL(15, 2) END GO if not exists(select * from T_POPEDOM where F_VALUE = 294) BEGIN INSERT INTO T_POPEDOM (F_ID, F_NAME,F_VALUE, F_REMARK,F_IsTempPopedom) VALUES (1850,'消费券结账', 294, NULL, NULL); END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_DELETE_CHECKOUT') and Name='F_ExpenseCardID') BEGIN ALTER TABLE T_DELETE_CHECKOUT ADD F_ExpenseCardID varchar(20) END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_DELETE_CHECKOUT') and Name='F_ExpenseCardCode') BEGIN ALTER TABLE T_DELETE_CHECKOUT ADD F_ExpenseCardCode varchar(20) END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_DELETE_RECEIVE') and Name='F_ExpenseCardID') BEGIN ALTER TABLE T_DELETE_RECEIVE ADD F_ExpenseCardID varchar(20) END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_DELETE_RECEIVE') and Name='F_ExpenseCardCode') BEGIN ALTER TABLE T_DELETE_RECEIVE ADD F_ExpenseCardCode varchar(20) END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_DELETE_CONSUME') and Name='F_ExpenseCardID') BEGIN ALTER TABLE T_DELETE_CONSUME ADD F_ExpenseCardID varchar(20) END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_DELETE_CONSUME') and Name='F_ExpenseCardCode') BEGIN ALTER TABLE T_DELETE_CONSUME ADD F_ExpenseCardCode varchar(20) END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_DELETE_CONSUME') and Name='F_ROOMAREA') BEGIN ALTER TABLE T_DELETE_CONSUME ADD F_ROOMAREA varchar(20) END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_HANDBRAND') and Name='F_BindDateTime') BEGIN ALTER TABLE T_HANDBRAND ADD F_BindDateTime DATETIME END if exists(select name from sysobjects where name='V_CONSUMENOTCHECKOUT') DROP VIEW [V_CONSUMENOTCHECKOUT] GO CREATE VIEW [dbo].[V_CONSUMENOTCHECKOUT] AS SELECT dbo.T_CONSUME.F_ID, dbo.T_CONSUME.F_RECEIVEID, dbo.T_CONSUME.F_GOODSID, dbo.T_CONSUME.F_PRICE, dbo.T_CONSUME.F_AMOUNT, dbo.T_CONSUME.F_ISPRESENT, dbo.T_CONSUME.F_CANREBATE, dbo.T_CONSUME.F_REBATE, dbo.T_CONSUMEWORKER.F_HEADSHIPID, dbo.T_HEADSHIP.F_NAME AS F_HEADSHIPNAME, dbo.T_CONSUMEWORKER.F_WORKERID, dbo.T_CONSUMEWORKER.F_ISNAMED, dbo.T_CONSUME.F_REMARK, dbo.T_CONSUME.F_USERID, dbo.T_CONSUME.F_COST, dbo.T_CONSUME.F_STATE, dbo.T_GOODS.F_NAME, dbo.T_GOODS.F_GOODSTYPEID, dbo.T_GOODSTYPE.F_NAME AS F_GOODSTYPENAME, dbo.T_GOODS.F_GOODSTYPE1ID, dbo.T_GOODSTYPE1.F_NAME AS F_GOODSTYPE1NAME, dbo.T_GOODSTYPE1.F_STATE AS F_GOODSTYPE1STATE, dbo.T_GOODSTYPE1.F_ISSTORAGE, dbo.T_RECEIVE.F_DATETIME, dbo.T_RECEIVE.F_CHECKOUTID, dbo.T_RECEIVE.F_SHOPID, dbo.T_RECEIVE.F_HANDBRANDID, dbo.T_HANDBRAND.F_SHOWID AS F_HANDBRANDSHOWID, dbo.T_RECEIVE.F_ROOMID, dbo.T_ROOM.F_SHOWID AS F_ROOMSHOWID, dbo.T_RECEIVE.F_JOINID, dbo.T_RECEIVE.F_AMOUNT AS F_PERSONTOTALAMOUNT, dbo.T_RECEIVE.F_MALEAMOUNT, dbo.T_CONSUME.F_STARTDATETIME, dbo.T_CONSUME.F_ENDDATETIME, dbo.T_GOODS.F_STARTTIMES, dbo.T_GOODS.F_STARTTIMESPRICE, dbo.T_GOODS.F_TIMES, dbo.T_GOODS.F_TIMESPRICE, dbo.T_CONSUMEWORKER.F_ID AS F_CONSUMEWORKERID, dbo.T_CONSUME.F_ENTERDATETIME, (CASE WHEN T_RECEIVE.F_MEMORANDUM IS NOT NULL THEN T_RECEIVE.F_MEMORANDUM ELSE dbo.T_RECEIVE.F_REMARK END) AS F_RECEIVEREMARK, dbo.T_CONSUMEWORKER.F_GOODSDEDUCTAUTOID, dbo.T_CONSUMEWORKER.F_OUTDATETIME, dbo.T_CONSUMEWORKER.F_BACKDATETIME, dbo.T_CONSUME.F_MINISTERID, dbo.T_GOODS.F_ISSMALL, dbo.T_CONSUMEWORKER.F_ISADDCLOCK, dbo.T_CONSUME.F_DELAY, dbo.T_CONSUME.F_DELAYID, dbo.T_RECEIVE.F_PRIORCHECKOUTID, dbo.T_CONSUME.F_ISPRIOR, dbo.T_CONSUMEWORKER.F_WALKTHROUGHINDEX, dbo.T_CONSUMEWORKER.F_ISSTOPWALKTHROUGH, dbo.T_CONSUMEWORKER.F_ENTERDATETIME AS F_WORKERENTERDATETIME, dbo.T_GOODS.F_HEADSHIPTRAIT AS F_GOODSHEADSHIPTRAIT, dbo.T_RECEIVE.F_ISSTOPAUTOTEA, dbo.T_WORKER.F_ID AS F_SHOWWORKERID, t1.F_ID AS F_SHOWMINISTERID, dbo.T_GOODS.F_ISEXTERNALSOUND, T_CONSUME.F_SATISFYCOMSUMEWORKERID, dbo.T_RECEIVE.F_CLIENTID, dbo.T_CONSUME.F_SEATNUMBER, dbo.T_CONSUME.F_HEADSHIPLEVEL, dbo.T_CONSUME.F_GOODSPLUSPARENTID, dbo.T_CONSUME.F_ExpenseCardID, dbo.T_CONSUME.F_ExpenseCardCode, T_ROOM.F_ROOMTYPEID FROM dbo.T_RECEIVE INNER JOIN dbo.T_CONSUME ON dbo.T_RECEIVE.F_ID = dbo.T_CONSUME.F_RECEIVEID AND dbo.T_RECEIVE.F_CHECKOUTID IS NULL LEFT OUTER JOIN dbo.T_CONSUMEWORKER ON dbo.T_CONSUME.F_ID = dbo.T_CONSUMEWORKER.F_CONSUMEID LEFT OUTER JOIN dbo.T_HEADSHIP ON dbo.T_CONSUMEWORKER.F_HEADSHIPID = dbo.T_HEADSHIP.F_ID LEFT OUTER JOIN dbo.T_GOODS ON dbo.T_CONSUME.F_GOODSID = dbo.T_GOODS.F_ID LEFT OUTER JOIN dbo.T_GOODSTYPE ON dbo.T_GOODS.F_GOODSTYPEID = dbo.T_GOODSTYPE.F_ID LEFT OUTER JOIN dbo.T_GOODSTYPE1 ON dbo.T_GOODS.F_GOODSTYPE1ID = dbo.T_GOODSTYPE1.F_ID LEFT OUTER JOIN dbo.T_ROOM ON dbo.T_RECEIVE.F_ROOMID = dbo.T_ROOM.F_ID LEFT OUTER JOIN dbo.T_HANDBRAND ON dbo.T_RECEIVE.F_HANDBRANDID = dbo.T_HANDBRAND.F_ID LEFT OUTER JOIN dbo.T_WORKER ON dbo.T_CONSUMEWORKER.F_WORKERID = dbo.T_WORKER.F_ARCHIVESID LEFT OUTER JOIN dbo.T_WORKER AS t1 ON dbo.T_CONSUME.F_MINISTERID = t1.F_ARCHIVESID GO --短信队列 IF OBJECT_ID (N'T_SMSQueue') IS NULL BEGIN --如果不存在该表，则进行创建 CREATE TABLE [dbo].[T_SMSQueue]( [F_ID] [VARCHAR](20) NOT NULL, [F_KEYID] [VARCHAR](20) NULL, [F_TELEPHONE] [VARCHAR](50) NULL, [F_CARDID] [VARCHAR](50) NULL, [F_DATETIME] [DATETIME] NULL, [F_MONEY] [DECIMAL](9, 2) NULL, [F_Balance] [DECIMAL](9, 2) NULL, [F_SIGN] [VARCHAR](50) NULL, [F_STATUS] [INT] NULL, [F_SENDTIME] [DATETIME] NULL, [F_ERRORTIMES] [INT] NULL, [F_LASTMSG] [VARCHAR](5000) NULL, [F_TYPE] [VARCHAR](10) NULL, [F_SHOPID] [VARCHAR](10) NULL, CONSTRAINT [PK_T_SMSQueue] 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] END GO --短信账号 IF OBJECT_ID (N'T_SMS') IS NULL BEGIN --如果不存在该表，则进行创建 CREATE TABLE [dbo].[T_SMS]( [F_ID] [varchar](10) NOT NULL, [F_USERNAME] [varchar](50) NULL, [F_PASSWORD] [varchar](50) NULL, [F_SHOPID] [varchar](10) NULL, [F_AMOUNT] [int] NULL, [F_LASTSENDDATETIME] [datetime] NULL, [F_LASTMSG] [varchar](1000) NULL, [F_SENDAMOUNT] [int] NULL, [F_SUCCESS] [int] NULL, [F_TOTALAMOUNT] [int] NULL, CONSTRAINT [PK_T_SMS] 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, FILLFACTOR = 95) ON [PRIMARY] ) ON [PRIMARY] END GO IF OBJECT_ID (N'T_SMS') IS NULL BEGIN --如果不存在该表，则进行创建 CREATE TABLE [dbo].[T_SMS]( [F_ID] [VARCHAR](10) NOT NULL, [F_USERNAME] [VARCHAR](50) NULL, [F_PASSWORD] [VARCHAR](50) NULL, [F_SHOPID] [VARCHAR](10) NULL, [F_AMOUNT] [INT] NULL, [F_LASTSENDDATETIME] [DATETIME] NULL, [F_LASTMSG] [VARCHAR](1000) NULL, [F_SENDAMOUNT] [INT] NULL, [F_SUCCESS] [INT] NULL, [F_TOTALAMOUNT] [INT] NULL, CONSTRAINT [PK_T_SMS] 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, FILLFACTOR = 95) ON [PRIMARY] ) ON [PRIMARY] END Go IF OBJECT_ID (N'T_SMSQueue') IS NULL BEGIN --如果不存在该表，则进行创建 CREATE TABLE [dbo].[T_SMSQueue]( [F_ID] [VARCHAR](20) NOT NULL, [F_KEYID] [VARCHAR](20) NULL, [F_TELEPHONE] [VARCHAR](50) NULL, [F_CARDID] [VARCHAR](50) NULL, [F_DATETIME] [DATETIME] NULL, [F_MONEY] [DECIMAL](9, 2) NULL, [F_Balance] [DECIMAL](9, 2) NULL, [F_SIGN] [VARCHAR](50) NULL, [F_STATUS] [INT] NULL, [F_SENDTIME] [DATETIME] NULL, [F_ERRORTIMES] [INT] NULL, [F_LASTMSG] [VARCHAR](5000) NULL, [F_TYPE] [VARCHAR](10) NULL, [F_SHOPID] [VARCHAR](10) NULL, CONSTRAINT [PK_T_SMSQueue] 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] END GO --发票管理添加三个会员字段 IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_INVOICE') and Name='F_CLIENTID') BEGIN ALTER TABLE T_INVOICE ADD F_CLIENTID varchar(20) NULL END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_INVOICE') and Name='F_CLIENTNAME') BEGIN ALTER TABLE T_INVOICE ADD F_CLIENTNAME varchar(20) NULL END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_INVOICE') and Name='F_MOVETEL') BEGIN ALTER TABLE T_INVOICE ADD F_MOVETEL varchar(50) NULL END GO --加入房间区域 if exists(select name from sysobjects where name='V_CONSUME') DROP VIEW V_CONSUME GO CREATE VIEW V_CONSUME AS SELECT T_CONSUME.F_ID, T_CHECKOUT.F_ID AS F_CHECKOUTID, T_CHECKOUT.F_DATETIME, T_CHECKOUT.F_USERID AS F_CHECKOUTUSERID, 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, T_CHECKOUT.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_TICKETMONEY, T_CONSUME.F_SEATNUMBER, T_CONSUME.F_ExpenseCardID, T_CONSUME.F_ExpenseCardCode, T_CONSUME.F_ROOMAREA FROM T_CONSUME INNER JOIN T_RECEIVE ON T_CONSUME.F_RECEIVEID = T_RECEIVE.F_ID INNER JOIN T_CHECKOUT ON T_RECEIVE.F_CHECKOUTID = T_CHECKOUT.F_ID LEFT OUTER JOIN T_GOODS ON T_CONSUME.F_GOODSID = T_GOODS.F_ID LEFT OUTER JOIN T_GOODSTYPE ON T_GOODS.F_GOODSTYPEID = T_GOODSTYPE.F_ID LEFT OUTER JOIN T_GOODSTYPE1 ON T_GOODS.F_GOODSTYPE1ID = T_GOODSTYPE1.F_ID GO if exists(select name from sysobjects where name='V_CONSUMEALL') DROP VIEW V_CONSUMEALL 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, 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 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 exists(select name from sysobjects where name='V_GRATISTCONSUME') DROP VIEW V_GRATISTCONSUME GO CREATE VIEW V_GRATISTCONSUME AS SELECT T_GRATISTCONSUME.F_ID, T_RECEIVE.F_DATETIME AS F_RECEIVEDATETIME, T_GRATISTCONSUME.F_RECEIVEID, T_GRATISTCONSUME.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_GRATISTCONSUME.F_PRICE, T_GRATISTCONSUME.F_AMOUNT, T_GRATISTCONSUME.F_REMARK, T_RECEIVE.F_USERID, T_RECEIVE.F_SHOPID, T_GRATISTCONSUME.F_STATE, T_RECEIVE.F_HANDBRANDID, T_RECEIVE.F_ROOMID, T_TEABILL.F_STARTDATETIME, T_TEABILL.F_ENDDATETIME, T_TEABILL.F_WORKERID, T_WORKER.F_ID AS F_SHOWWORKERID, T_RECEIVE.F_CHECKOUTID, T_GRATISTCONSUME.F_STARTDATETIME AS F_ENTERDATETIME, T_CHECKOUT.F_DATETIME, T_GRATISTCONSUME.F_ROOMAREA FROM T_GRATISTCONSUME INNER JOIN T_TEABILL ON T_TEABILL.F_ID = T_GRATISTCONSUME.F_TEABILLID INNER JOIN T_GOODS ON T_GRATISTCONSUME.F_GOODSID = T_GOODS.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 INNER JOIN T_RECEIVE ON T_GRATISTCONSUME.F_RECEIVEID = T_RECEIVE.F_ID LEFT OUTER JOIN T_CHECKOUT ON T_RECEIVE.F_CHECKOUTID = T_CHECKOUT.F_ID LEFT OUTER JOIN T_WORKER ON T_GRATISTCONSUME.F_MINISTERID = T_WORKER.F_ARCHIVESID GO if exists(select name from sysobjects where name='V_GRATISTCONSUMEByCheckout') DROP VIEW V_GRATISTCONSUMEByCheckout GO CREATE VIEW V_GRATISTCONSUMEByCheckout AS SELECT T_GRATISTCONSUME.F_ID, T_CHECKOUT.F_DATETIME, T_GRATISTCONSUME.F_RECEIVEID, T_GRATISTCONSUME.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_GRATISTCONSUME.F_PRICE, T_GRATISTCONSUME.F_AMOUNT, T_GRATISTCONSUME.F_REMARK, T_RECEIVE.F_USERID, T_RECEIVE.F_SHOPID, T_GRATISTCONSUME.F_STATE, T_RECEIVE.F_HANDBRANDID, T_RECEIVE.F_ROOMID, T_TEABILL.F_STARTDATETIME, T_TEABILL.F_ENDDATETIME, T_TEABILL.F_WORKERID, T_WORKER.F_ID AS F_SHOWWORKERID, T_GRATISTCONSUME.F_STARTDATETIME AS F_ENTERDATETIME, T_GRATISTCONSUME.F_ROOMAREA FROM T_GRATISTCONSUME INNER JOIN T_TEABILL ON T_TEABILL.F_ID = T_GRATISTCONSUME.F_TEABILLID INNER JOIN T_GOODS ON T_GRATISTCONSUME.F_GOODSID = T_GOODS.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 INNER JOIN T_RECEIVE ON T_GRATISTCONSUME.F_RECEIVEID = T_RECEIVE.F_ID INNER JOIN T_CHECKOUT ON T_RECEIVE.F_CHECKOUTID = T_CHECKOUT.F_ID LEFT OUTER JOIN T_WORKER ON T_GRATISTCONSUME.F_MINISTERID = T_WORKER.F_ARCHIVESID GO if exists(select name from sysobjects where name='V_ROOM') DROP VIEW [V_ROOM] GO CREATE VIEW [dbo].[V_ROOM] AS SELECT t_room.f_id, t_room.f_showid, t_room.f_roomtypeid, t_roomtype.f_name f_roomtype, t_room.f_state, t_room.f_amount, t_room.f_checkinamount, t_room.f_remark, t_room.f_shopid, t_floor.f_autoid AS F_FLOORAUTOID, t_floor.f_name AS F_FLOORNAME, t_room.F_ISCHARGE, t_room.f_goodsid, T_GOODS.f_name AS F_GOODSNAME, t_room.F_ISTAKE, t_room.f_workerid, t_room.f_destinedatetime, t_room.F_HOMOPHONY, t_room.F_ISLEAST, t_room.F_LEAST, t_room.F_ISAUDIOMUTING, t_room.F_ISAUTOTEAREMIND, t_room.F_BangWeiRoomCode, t_room.F_ISSEAT, t_room.F_ROOMAREANAME FROM t_room INNER JOIN t_roomtype ON (t_room.f_roomtypeid = t_roomtype.f_id) LEFT JOIN t_floor ON (t_floor.f_autoid = t_room.f_floorautoid) LEFT JOIN T_GOODS ON (T_GOODS.f_id = T_ROOM.f_goodsid) ; GO if exists(select name from sysobjects where name='V_GRATISTCONSUME') DROP VIEW [V_GRATISTCONSUME] GO CREATE VIEW [dbo].[V_GRATISTCONSUME] AS SELECT T_GRATISTCONSUME.F_ID , T_RECEIVE.F_DATETIME AS F_RECEIVEDATETIME , T_GRATISTCONSUME.F_RECEIVEID , T_GRATISTCONSUME.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_GRATISTCONSUME.F_PRICE , T_GRATISTCONSUME.F_AMOUNT , T_GRATISTCONSUME.F_REMARK , T_RECEIVE.F_USERID AS F_RECEIVEUSERID , T_RECEIVE.F_SHOPID , T_GRATISTCONSUME.F_STATE , T_RECEIVE.F_HANDBRANDID , T_RECEIVE.F_ROOMID , T_TEABILL.F_STARTDATETIME , T_TEABILL.F_ENDDATETIME , T_TEABILL.F_WORKERID , T_WORKER.F_ID AS F_SHOWWORKERID , T_RECEIVE.F_CHECKOUTID , T_GRATISTCONSUME.F_STARTDATETIME AS F_ENTERDATETIME , T_CHECKOUT.F_DATETIME , T_GRATISTCONSUME.F_ROOMAREA, T_CHECKOUT.F_USERID AS F_USERID FROM T_GRATISTCONSUME INNER JOIN T_TEABILL ON T_TEABILL.F_ID = T_GRATISTCONSUME.F_TEABILLID INNER JOIN T_GOODS ON T_GRATISTCONSUME.F_GOODSID = T_GOODS.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 INNER JOIN T_RECEIVE ON T_GRATISTCONSUME.F_RECEIVEID = T_RECEIVE.F_ID LEFT OUTER JOIN T_CHECKOUT ON T_RECEIVE.F_CHECKOUTID = T_CHECKOUT.F_ID LEFT OUTER JOIN T_WORKER ON T_GRATISTCONSUME.F_MINISTERID = T_WORKER.F_ARCHIVESID; GO if exists(select name from sysobjects where name='V_CONSUMEALL') DROP VIEW [V_CONSUMEALL] GO CREATE VIEW [dbo].[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 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 exists(select name from sysobjects where name='V_CONSUMECHECKOUT') DROP VIEW [V_CONSUMECHECKOUT] GO CREATE VIEW [V_CONSUMECHECKOUT] AS SELECT dbo.T_CONSUME.F_ID, dbo.V_CHECKOUT.F_SKIPID, dbo.V_CHECKOUT.F_ID AS F_CHECKOUTID, dbo.V_CHECKOUT.F_DATETIME, dbo.T_CONSUME.F_RECEIVEID, dbo.T_CONSUME.F_GOODSID, dbo.T_GOODS.F_NAME AS F_GOODSNAME, dbo.T_GOODS.F_GOODSTYPEID, dbo.T_GOODSTYPE.F_NAME AS F_GOODSTYPENAME, dbo.T_GOODS.F_GOODSTYPE1ID, dbo.T_GOODSTYPE1.F_NAME AS F_GOODSTYPE1NAME, dbo.T_GOODS.F_PRICE AS F_GOODSPRICE, dbo.T_CONSUME.F_OLDPRICE, dbo.T_CONSUME.F_PRICE, dbo.T_CONSUME.F_AMOUNT, dbo.T_CONSUME.F_RECEIVABLESUM AS F_SUM, dbo.T_CONSUME.F_ISPRESENT, dbo.T_CONSUME.F_CANREBATE, dbo.T_CONSUME.F_REBATE, dbo.T_CONSUME.F_REMARK, dbo.V_CHECKOUT.F_USERID, dbo.V_CHECKOUT.F_SHOPID, dbo.V_CHECKOUT.F_CLIENTID, dbo.V_CHECKOUT.F_TYPE, dbo.T_CONSUME.F_COST, dbo.T_CONSUME.F_STATE, dbo.T_GOODS.F_STARTTIMES, dbo.T_CONSUME.F_STARTDATETIME, dbo.T_CONSUME.F_ENDDATETIME, dbo.T_RECEIVE.F_HANDBRANDID, dbo.T_RECEIVE.F_ROOMID, dbo.T_RECEIVE.F_AMOUNT AS F_PERSONTOTALAMOUNT, dbo.T_RECEIVE.F_MALEAMOUNT, dbo.T_RECEIVE.F_TRANSMITGROUP, dbo.T_CONSUME.F_ISPROMOTION, dbo.T_CONSUME.F_ISTOGO, dbo.T_CONSUME.F_ISTIME, dbo.T_CONSUME.F_TIMEMEMBERID, dbo.T_CONSUME.F_CLIENTREBATE, dbo.T_CONSUME.F_CLIENTGOODSREBATE, dbo.T_CONSUME.F_PROMOTIONREBATE, dbo.T_CONSUME.F_CHECKOUTREBATE, ( CASE WHEN t_consume.f_istime = 1 THEN t_consume.f_oldsum ELSE t_consume.f_gatheringsum END ) AS F_GATHERINGSUM, dbo.T_CONSUME.F_OLDSUM, dbo.T_CONSUME.F_RECEIVABLESUM, dbo.T_CONSUME.F_WORKERDEDUCT, dbo.T_UNIT.F_NAME AS F_UNITNAME, dbo.T_CONSUME.F_TURNREMARK, dbo.T_CONSUME.F_MINISTERID, dbo.T_CONSUME.F_GATHERINGSUM * dbo.T_CONSUME.F_CLIENTREBATE / 100 AS F_PRACTICESUM, dbo.V_CHECKOUT.F_HANDWORKID, dbo.T_CARDTYPE.F_ID AS F_CARDTYPEID, dbo.T_CARDTYPE.F_NAME AS F_CARDNAME, CAST ( COALESCE ( dbo.V_CONSUMEWORKERFORCONSUME.F_ISNAMED, 0 ) AS INTEGER ) AS F_ISNAMED, CAST ( COALESCE ( dbo.V_CONSUMEWORKERFORCONSUME.F_ISADDCLOCK, 0 ) AS INTEGER ) AS F_ISADDCLOCK, dbo.T_CONSUME.F_TICKETMONEY, T_CLIENT.F_REBATE AS F_CLIENTFACTREBATE, T_BathAmount.F_Amount AS F_ALLAMOUNT, T_BathAmount.F_GiveAmount, T_BathAmount.F_ManJianAmount, dbo.T_HANDBRAND.F_SHOWID AS F_HANDBRANDSHOWID , dbo.T_ROOM.F_SHOWID AS F_ROOMSHOWID , dbo.T_GOODS.F_SHOWID AS F_GOODSSHOWID , dbo.T_WORKER.F_ID AS F_MINISTERSHOWID FROM dbo.T_CONSUME INNER JOIN dbo.T_RECEIVE ON ( dbo.T_CONSUME.F_RECEIVEID = dbo.T_RECEIVE.F_ID AND dbo.T_CONSUME.F_GOODSID <> 'StopClock') INNER JOIN dbo.V_CHECKOUT ON dbo.T_RECEIVE.F_CHECKOUTID = dbo.V_CHECKOUT.F_ID LEFT OUTER JOIN dbo.T_GOODS ON dbo.T_CONSUME.F_GOODSID = dbo.T_GOODS.F_ID LEFT OUTER JOIN dbo.T_GOODSTYPE ON dbo.T_GOODS.F_GOODSTYPEID = dbo.T_GOODSTYPE.F_ID LEFT OUTER JOIN dbo.T_GOODSTYPE1 ON dbo.T_GOODS.F_GOODSTYPE1ID = dbo.T_GOODSTYPE1.F_ID LEFT OUTER JOIN dbo.T_UNIT ON dbo.T_GOODS.F_UNITID = dbo.T_UNIT.F_ID LEFT OUTER JOIN dbo.T_CLIENT ON dbo.T_CLIENT.F_ID = dbo.V_CHECKOUT.F_CLIENTID LEFT OUTER JOIN dbo.T_CARDTYPE ON dbo.T_CLIENT.F_CARDTYPEID = dbo.T_CARDTYPE.F_ID LEFT OUTER JOIN dbo.V_CONSUMEWORKERFORCONSUME ON dbo.V_CONSUMEWORKERFORCONSUME.F_CONSUMEID = dbo.T_CONSUME.F_ID LEFT JOIN dbo.T_BathAmount ON ( T_BathAmount.F_CONSUMEID = T_CONSUME.F_ID ) LEFT OUTER JOIN dbo.T_ROOM ON dbo.T_RECEIVE.F_ROOMID = dbo.T_ROOM.F_ID LEFT OUTER JOIN dbo.T_HANDBRAND ON dbo.T_RECEIVE.F_HANDBRANDID = dbo.T_HANDBRAND.F_ID LEFT OUTER JOIN dbo.T_WORKER ON dbo.T_CONSUME.F_MINISTERID = dbo.T_WORKER.F_ARCHIVESID; ; GO if not exists(select * from T_POPEDOM where F_ID = '360') BEGIN INSERT INTO T_POPEDOM (F_ID, F_NAME, F_VALUE) VALUES (360, '可改负数量',295); END GO if not exists(select * from T_POPEDOM where F_ID = '180') BEGIN INSERT INTO T_POPEDOM (F_ID, F_NAME, F_VALUE) VALUES (180, '允许空手牌开单',296); END GO IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_CONSUMEWORKERCHECKOUT]')) DROP VIEW [dbo].[V_CONSUMEWORKERCHECKOUT] GO CREATE VIEW [dbo].[V_CONSUMEWORKERCHECKOUT] AS SELECT dbo.V_CHECKOUT.F_SKIPID, dbo.V_CHECKOUT.F_DATETIME, dbo.T_CONSUMEWORKER.F_WORKERID, dbo.T_WORKER.F_SHOPID AS F_WORKERSHOPID, dbo.T_WORKER.F_NAME, dbo.T_WORKER.F_HEADSHIPID, dbo.T_HEADSHIP.F_NAME AS F_HEADSHIPNAME, dbo.T_CONSUMEWORKER.F_HEADSHIPID AS F_CONSUMEWORKERHEADSHIPID, T_HEADSHIP1.F_NAME AS F_CONSUMEWORKERHEADSHIPNAME, dbo.T_CONSUMEWORKER.F_ISNAMED, dbo.T_CONSUMEWORKER.F_MONEYFACTOR, dbo.T_CONSUMEWORKER.F_DEDUCTFACTOR, dbo.T_CONSUMEWORKER.F_DEDUCTTYPE, dbo.T_CONSUMEWORKER.F_DEDUCT, dbo.T_CONSUMEWORKER.F_MONEY, dbo.T_CONSUME.F_GOODSID, dbo.T_GOODS.F_NAME AS F_GOODNAME, dbo.T_GOODSTYPE.F_ID AS F_GOODTYPEID, dbo.T_GOODSTYPE.F_NAME AS F_GOODTYPENAME, dbo.T_CONSUME.F_RECEIVABLESUM, (CASE WHEN t_consume.f_istime = 1 THEN t_consume.f_oldsum ELSE t_consume.f_gatheringsum END) AS F_GATHERINGSUM, dbo.T_CONSUME.F_AMOUNT, dbo.T_CONSUME.F_USERID, dbo.V_CHECKOUT.F_SHOPID, dbo.T_WORKER.F_STATE AS F_WORKERSTATE, dbo.V_CHECKOUT.F_ID AS F_CHECKOUTID, dbo.T_CONSUME.F_ID AS F_CONSUMEID, dbo.T_CONSUMEWORKER.F_RECEIVEID, dbo.V_CHECKOUT.F_CLIENTID, dbo.V_CHECKOUT.F_TYPE, dbo.T_RECEIVE.F_HANDBRANDID, dbo.T_RECEIVE.F_ROOMID, dbo.T_CONSUMEWORKER.F_OUTDATETIME, dbo.T_CONSUMEWORKER.F_BACKDATETIME, dbo.V_CHECKOUT.F_HANDWORKID, dbo.T_GOODSTYPE1.F_ID AS F_GOODTYPE1ID, dbo.T_GOODSTYPE1.F_NAME AS F_GOODTYPE1NAME, dbo.T_WORKER.F_SEX, dbo.T_CONSUMEWORKER.F_ISADDCLOCK, dbo.T_CONSUMEWORKER.F_ID AS F_CONSUMEWORKERID, dbo.T_WORKERSATISFIED.F_MONEY AS F_SATISFIEDMONEY, (CASE WHEN ISNULL(t_workersatisfied.f_money, 0) > 0 THEN 1 ELSE 0 END) AS F_ISSATISFIED, dbo.T_CONSUMEWORKER.F_ENTERDATETIME, dbo.T_WORKER.F_ID AS F_SHOWWORKERID, dbo.T_RECEIVE.F_DATETIME AS F_RECEIVEDATETIME, dbo.V_CHECKOUT.F_EFFECT AS F_CHECKOUTEFFECT, dbo.T_WORKERSATISFIED.F_EFFECT AS F_SATISFIEDEFFECT, dbo.T_CONSUMEWORKER.F_SelClock, dbo.T_CONSUME.F_STARTDATETIME, dbo.T_CONSUME.F_ENDDATETIME, t_workercopy.F_ID AS F_MINISTERID, (CASE WHEN T_WQSTTOTAL.F_TYPE = '加班' THEN 1 ELSE 0 END) AS F_ISOVERTIME, dbo.T_ROOM.F_SHOWID AS F_ROOMSHOWID, dbo.T_HANDBRAND.F_SHOWID AS F_HANDBRANDSHOWID, dbo.T_GOODS.F_SHOWID AS F_GOODSSHOWID FROM dbo.T_CONSUMEWORKER INNER JOIN dbo.T_CONSUME ON dbo.T_CONSUMEWORKER.F_CONSUMEID = dbo.T_CONSUME.F_ID AND dbo.T_CONSUME.F_GOODSID <> 'StopClock' INNER JOIN dbo.T_RECEIVE ON dbo.T_CONSUMEWORKER.F_RECEIVEID = dbo.T_RECEIVE.F_ID INNER JOIN dbo.V_CHECKOUT ON dbo.T_RECEIVE.F_CHECKOUTID = dbo.V_CHECKOUT.F_ID INNER JOIN dbo.T_WORKER ON dbo.T_CONSUMEWORKER.F_WORKERID = dbo.T_WORKER.F_ARCHIVESID LEFT OUTER JOIN dbo.T_WORKERSATISFIED ON dbo.T_CONSUMEWORKER.F_ID = dbo.T_WORKERSATISFIED.F_CONSUMEWORKERID LEFT OUTER JOIN dbo.T_HEADSHIP ON dbo.T_WORKER.F_HEADSHIPID = dbo.T_HEADSHIP.F_ID LEFT OUTER JOIN dbo.T_HEADSHIP AS T_HEADSHIP1 ON T_HEADSHIP1.F_ID = dbo.T_CONSUMEWORKER.F_HEADSHIPID INNER JOIN dbo.T_GOODS ON dbo.T_CONSUME.F_GOODSID = dbo.T_GOODS.F_ID INNER JOIN dbo.T_GOODSTYPE ON dbo.T_GOODS.F_GOODSTYPEID = dbo.T_GOODSTYPE.F_ID INNER JOIN dbo.T_GOODSTYPE1 ON dbo.T_GOODS.F_GOODSTYPE1ID = dbo.T_GOODSTYPE1.F_ID LEFT OUTER JOIN dbo.T_HANDBRAND ON dbo.T_RECEIVE.F_HANDBRANDID = dbo.T_HANDBRAND.F_ID LEFT OUTER JOIN dbo.T_ROOM ON dbo.T_RECEIVE.F_ROOMID = dbo.T_ROOM.F_ID LEFT OUTER JOIN dbo.T_WORKER AS t_workercopy ON t_workercopy.F_ARCHIVESID = dbo.T_CONSUME.F_MINISTERID LEFT OUTER JOIN (SELECT F_CONSUMEWORKERID, F_TYPE FROM dbo.T_WORKERQUEUESTATISTICS UNION ALL SELECT F_CONSUMEWORKERID, F_TYPE FROM dbo.T_WORKERQUEUESTATISTICS_H) AS T_WQSTTOTAL ON T_WQSTTOTAL.F_CONSUMEWORKERID = dbo.T_CONSUMEWORKER.F_ID GO IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_MINISTERANALYSIS]')) DROP VIEW [dbo].[V_MINISTERANALYSIS] GO CREATE VIEW [dbo].[V_MINISTERANALYSIS] AS SELECT TOP (100) PERCENT dbo.T_CONSUME.F_RECEIVEID, dbo.T_CONSUME.F_PRICE, dbo.T_CONSUME.F_AMOUNT, dbo.T_CONSUME.F_ISPRESENT, dbo.T_CONSUME.F_CANREBATE, dbo.T_CONSUME.F_REBATE, dbo.T_CONSUME.F_USERID, dbo.T_CONSUME.F_COST, dbo.T_CONSUME.F_STARTDATETIME, dbo.T_CONSUME.F_ENDDATETIME, dbo.T_CONSUME.F_ISPROMOTION, dbo.T_CONSUME.F_ISTOGO, dbo.T_CONSUME.F_ISTIME, dbo.T_CONSUME.F_OLDPRICE, dbo.T_CONSUME.F_CLIENTREBATE, dbo.T_CONSUME.F_CLIENTGOODSREBATE, dbo.T_CONSUME.F_ENTERDATETIME, dbo.T_CONSUME.F_CLOCKINFO, dbo.T_CONSUME.F_MINISTERGOODSID, dbo.T_CONSUME.F_ROOMAREA, dbo.T_CONSUME.F_SEATNUMBER, dbo.T_CONSUME.F_HEADSHIPLEVEL, dbo.T_CONSUME.F_GOODSPLUSPARENTID, dbo.T_CONSUME.F_GATHERINGSUM, dbo.T_CONSUME.F_WORKERDEDUCT, dbo.T_CONSUMEWORKER.F_ISNAMED, dbo.T_CONSUMEWORKER.F_SelClock, dbo.T_CONSUMEWORKER.F_ISADDCLOCK, dbo.T_CONSUMEWORKER.F_HEADSHIPID AS F_WORKERHEADSHIPID, T_WORKER_1.F_ID AS F_MINISTERSHOWID, T_WORKER_1.F_NAME AS F_MINISTERNAME, dbo.T_WORKER.F_NAME AS F_WORKERNAME, dbo.T_WORKER.F_ID AS F_WORKERSHOWID, dbo.T_HEADSHIP.F_NAME AS F_WORKERHEADSHIPNAME, T_WORKER_1.F_SEX AS F_MINISTERSEX, dbo.T_WORKER.F_SEX AS F_WORKERSEX, T_GOODS_1.F_NAME AS F_GOODSNAME, T_GOODS_1.F_SHOWID AS F_GOODSSHOWID, dbo.T_RECEIVE.F_DATETIME AS F_RECEIVEDATETIME, dbo.T_CONSUME.F_MINISTERID, dbo.T_GOODS.F_NAME AS F_MINISTERGOODSNAME, dbo.T_GOODS.F_SHOWID AS F_MINISTERGOODSSHOWID, dbo.T_RECEIVE.F_SHOPID FROM dbo.T_CONSUME INNER JOIN dbo.T_CONSUMEWORKER ON dbo.T_CONSUME.F_ID = dbo.T_CONSUMEWORKER.F_CONSUMEID INNER JOIN dbo.T_RECEIVE ON dbo.T_CONSUME.F_RECEIVEID = dbo.T_RECEIVE.F_ID LEFT OUTER JOIN dbo.T_GOODS ON dbo.T_CONSUME.F_MINISTERGOODSID = dbo.T_GOODS.F_ID LEFT OUTER JOIN dbo.T_GOODS AS T_GOODS_1 ON dbo.T_CONSUME.F_GOODSID = T_GOODS_1.F_ID LEFT OUTER JOIN dbo.T_HEADSHIP ON dbo.T_CONSUMEWORKER.F_HEADSHIPID = dbo.T_HEADSHIP.F_ID LEFT OUTER JOIN dbo.T_WORKER AS T_WORKER_1 ON dbo.T_CONSUME.F_MINISTERID = T_WORKER_1.F_ARCHIVESID LEFT OUTER JOIN dbo.T_WORKER ON dbo.T_CONSUMEWORKER.F_WORKERID = dbo.T_WORKER.F_ARCHIVESID ORDER BY dbo.T_CONSUME.F_MINISTERID GO if not exists(select * from T_WORKERPOPEDOM where F_ID = '350') BEGIN INSERT INTO T_WORKERPOPEDOM VALUES(350, '允许退茶水操作', 11, NULL, 1); END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_EXTERNALVOICE') and Name='F_ISSENDVOICE') BEGIN ALTER TABLE T_EXTERNALVOICE ADD F_ISSENDVOICE INTEGER DEFAULT 1 END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_EXTERNALVOICE') and Name='F_ISSENDSWITCH') BEGIN ALTER TABLE T_EXTERNALVOICE ADD F_ISSENDSWITCH INTEGER DEFAULT 0 END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_EXTERNALVOICE') and Name='F_ControlBit') BEGIN ALTER TABLE T_EXTERNALVOICE ADD F_ControlBit VARCHAR(10) END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_EXTERNALVOICE') and Name='F_SwitchDelay') BEGIN ALTER TABLE T_EXTERNALVOICE ADD F_SwitchDelay INTEGER DEFAULT 0 END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_EXTERNALVOICE') and Name='F_ControlBit') BEGIN ALTER TABLE T_EXTERNALVOICE ADD F_ControlBit VARCHAR(10) END GO IF EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_EXTERNALVOICE') and Name='F_FLOWNAME') BEGIN ALTER TABLE T_EXTERNALVOICE ALTER COLUMN F_FLOWNAME VARCHAR(50) END GO UPDATE T_EXTERNALVOICE SET F_ISSENDVOICE = 1, F_ISSENDSWITCH = 0; GO --本地删除接待单后 同步删除远程的接待单 IF OBJECT_ID (N'T_SYN_RECEIVEACTION') IS NULL BEGIN --如果不存在该表，则进行创建 CREATE TABLE [dbo].[T_SYN_RECEIVEACTION]( [F_ID] [VARCHAR](20) NOT NULL, [F_RECEIVEID] [VARCHAR](20) NULL, [F_RECEIVEDATETIME] [DATETIME] NULL, [F_DATETIME] [DATETIME] NULL, [F_UPDATETIME] [DATETIME] NULL, [F_REMARK] [VARCHAR](500) NULL, [F_STATE] [INT] NULL, [F_SHOPID] [VARCHAR] (10) NULL CONSTRAINT [PK_T_RECEIVEACTION] 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] END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_EXTERNALVOICE') and Name='F_SwitchBit') BEGIN ALTER TABLE T_EXTERNALVOICE ADD F_SwitchBit VARCHAR(10) END GO IF NOT EXISTS ( SELECT * FROM T_WORKERQUEUEPARAMETER WHERE F_ID = 'sVersionFileName' ) BEGIN INSERT INTO T_WORKERQUEUEPARAMETER ( F_ID , F_VALUE , F_SHOPID , F_REMARK , F_ISHIDE , F_TYPE ) VALUES ( 'sVersionFileName' , '' , NULL , '单店系统小版本' , 0 , '系统' ); END; GO IF NOT EXISTS ( SELECT * FROM T_SYSTEM WHERE F_ID = 'sVersionFileName' ) BEGIN INSERT INTO T_SYSTEM ( F_ID , F_VALUE , F_REMARK , F_TYPE ) VALUES ( 'sVersionFileName' , '' , '软件系统小版本' , '系统' ); END; GO IF NOT EXISTS ( SELECT * FROM T_WORKERQUEUEPARAMETER WHERE F_ID = 'IsUseRoomStateWithFilter' ) BEGIN INSERT INTO T_WORKERQUEUEPARAMETER ( F_ID , F_VALUE , F_SHOPID , F_REMARK , F_ISHIDE , F_TYPE, F_VERSION, F_ADDDATE ) VALUES ( 'IsUseRoomStateWithFilter' , '0' ,NULL ,'启用高级房态界面' , 0 ,'系统','17.2.105', '2017-02-08'); END; GO IF EXISTS (select * from dbo.sysobjects where id =object_id(N'[dbo].[GETROOMSTATEANDCOSTTIME]') and xtype in (N'FN', N'IF', N'TF')) BEGIN drop function GETROOMSTATEANDCOSTTIME; END GO CREATE FUNCTION [dbo].[GETROOMSTATEANDCOSTTIME] ( @shopid VARCHAR(10) , @DefaultGoods VARCHAR(100) , @DefaultTeaType VARCHAR(100) ) RETURNS @RoomInfo TABLE ( F_ID VARCHAR(10) , F_ROOMTYPEID VARCHAR(10) , F_STATE VARCHAR(10) , F_AMOUNT INTEGER , F_CHECKINAMOUNT INTEGER , F_REMARK VARCHAR(250) , F_SHOPID VARCHAR(10) , F_FLOORAUTOID INTEGER , F_SHOWID VARCHAR(10) , F_ISCHARGE INTEGER , F_GOODSID VARCHAR(30) , F_ISTAKE INTEGER , F_WORKERID VARCHAR(10) , F_DESTINEDATETIME DATETIME , F_HOMOPHONY VARCHAR(20) , F_ISLEAST SMALLINT , F_LEAST DECIMAL(15, 2) , F_NODOAMOUNT INTEGER , F_CHECKIN INTEGER , F_OVERAMOUNT INTEGER , F_COSTTIME INTEGER , F_TOTALVACANCY INTEGER , F_FEMALEVACANCY INTEGER , F_MALEVACANCY INTEGER , F_ISAUDIOMUTING INTEGER , F_STATEPLUS VARCHAR(20) , F_ISCHECKOUTALL INTEGER , F_ISPRIORCHECKOUT INTEGER , F_ISREMINDSTOP INTEGER , F_ENDDATETIME DATETIME ) AS BEGIN DECLARE @ROOMCURSOR CURSOR , @CONSUMENOTCHECKOUT CURSOR; DECLARE @goodsid VARCHAR(30); DECLARE @amount DECIMAL(9, 2); DECLARE @startdatetime DATETIME; DECLARE @delay INTEGER; DECLARE @enddatetime DATETIME; DECLARE @enterdatetime DATETIME; DECLARE @costtime INTEGER; DECLARE @currdatetime DATETIME; DECLARE @costtimesum INTEGER; DECLARE @F_TEMPID VARCHAR(10); DECLARE @F_TEMPROOMTYPEID VARCHAR(10); DECLARE @F_TEMPSTATE VARCHAR(10); DECLARE @F_TEMPAMOUNT INTEGER; DECLARE @F_TEMPCHECKINAMOUNT INTEGER; DECLARE @F_TEMPREMARK VARCHAR(250); DECLARE @F_TEMPSHOPID VARCHAR(10); DECLARE @F_TEMPFLOORAUTOID INTEGER; DECLARE @F_TEMPSHOWID VARCHAR(10); DECLARE @F_TEMPISCHARGE INTEGER; DECLARE @F_TEMPGOODSID VARCHAR(30); DECLARE @F_TEMPISTAKE INTEGER; DECLARE @F_TEMPWORKERID VARCHAR(10); DECLARE @F_TEMPDESTINEDATETIME DATETIME; DECLARE @F_TEMPHOMOPHONY VARCHAR(20); DECLARE @F_TEMPISLEAST SMALLINT; DECLARE @F_TEMPLEAST DECIMAL(15, 2); DECLARE @F_TEMPNODOAMOUNT INTEGER; DECLARE @F_TEMPCheckIn INTEGER; DECLARE @F_TEMPOVERAMOUNT INTEGER; DECLARE @F_TEMPCOSTTIME INTEGER; DECLARE @F_TEMTTOTALVACANCY INTEGER; DECLARE @F_TEMTFEMALEVACANCY INTEGER; DECLARE @F_TEMTMALEVACANCY INTEGER; DECLARE @F_TEMPISAUDIOMUTING INTEGER; DECLARE @F_TEMPSTATEPLUS VARCHAR(20); DECLARE @F_TEMPISCHECKOUTALL INTEGER; DECLARE @F_TEMPISPRIORCHECKOUT INTEGER; DECLARE @F_TEMPISREMINDSTOP INTEGER; DECLARE @F_TEMPENDDATETIME DATETIME; DECLARE @starttime TIME; DECLARE @endtime TIME; DECLARE @GoogTimeAmount INTEGER; DECLARE @WorkerId VARCHAR(30); DECLARE @PRIORCHECKOUTID VARCHAR(20); DECLARE @ISSTOPAUTOTEA INTEGER; DECLARE @STARTTIMES INTEGER; DECLARE @GOODSTYPE1ID VARCHAR(20); DECLARE @ConsumeCount INTEGER; DECLARE @ReceiveCount INTEGER; SET @currdatetime = CURRENT_TIMESTAMP; SET @DefaultGoods = REPLACE(@DefaultGoods, '''', ''); SET @DefaultTeaType = REPLACE(@DefaultTeaType, '''', ''); SET @ROOMCURSOR = CURSOR FAST_FORWARD FOR SELECT F_ID, F_ROOMTYPEID, F_STATE, F_AMOUNT, F_CHECKINAMOUNT, F_REMARK, F_SHOPID, F_FLOORAUTOID, F_SHOWID, F_ISCHARGE, F_GOODSID, F_ISTAKE, F_WORKERID, F_DESTINEDATETIME, F_HOMOPHONY, F_ISLEAST, F_LEAST, F_ISAUDIOMUTING, F_StatePlus, F_ISCHECKOUTALL FROM T_ROOM WHERE F_SHOPID = @shopid; OPEN @ROOMCURSOR; FETCH NEXT FROM @ROOMCURSOR INTO @F_TEMPID, @F_TEMPROOMTYPEID, @F_TEMPSTATE, @F_TEMPAMOUNT, @F_TEMPCHECKINAMOUNT, @F_TEMPREMARK, @F_TEMPSHOPID, @F_TEMPFLOORAUTOID, @F_TEMPSHOWID, @F_TEMPISCHARGE, @F_TEMPGOODSID, @F_TEMPISTAKE, @F_TEMPWORKERID, @F_TEMPDESTINEDATETIME, @F_TEMPHOMOPHONY, @F_TEMPISLEAST, @F_TEMPLEAST, @F_TEMPISAUDIOMUTING, @F_TEMPSTATEPLUS, @F_TEMPISCHECKOUTALL; WHILE @@FETCH_STATUS = 0 BEGIN SET @F_TEMPNODOAMOUNT = 0; SET @F_TEMPCheckIn = 0; SET @F_TEMPOVERAMOUNT = 0; SET @F_TEMPCOSTTIME = 0; SET @costtimesum = 0; SET @F_TEMTTOTALVACANCY = 0; SET @F_TEMTFEMALEVACANCY = 0; SET @F_TEMTMALEVACANCY = 0; SET @F_TEMPISPRIORCHECKOUT = 0; SET @F_TEMPISREMINDSTOP = 0; SET @F_TEMPENDDATETIME = NULL; SET @CONSUMENOTCHECKOUT = CURSOR FOR SELECT F_GOODSID, F_AMOUNT, F_ENTERDATETIME, F_STARTDATETIME, F_ENDDATETIME,F_DELAY, F_WORKERID,F_PRIORCHECKOUTID,F_ISSTOPAUTOTEA, F_STARTTIMES, F_GOODSTYPE1ID FROM V_CONSUMENOTCHECKOUT WHERE F_SHOPID = @shopid AND F_ROOMID = @F_TEMPID; OPEN @CONSUMENOTCHECKOUT; FETCH NEXT FROM @CONSUMENOTCHECKOUT INTO @goodsid, @amount, @enterdatetime, @startdatetime, @enddatetime, @delay, @WorkerId, @PRIORCHECKOUTID, @ISSTOPAUTOTEA, @STARTTIMES, @GOODSTYPE1ID; WHILE @@FETCH_STATUS = 0 BEGIN IF ( @STARTTIMES > 0 ) AND ( @GOODSTYPE1ID NOT IN ( @DefaultTeaType ) ) BEGIN --计算房间：未点单数量，项目数量，完成数量 IF ( @goodsid IN ( @DefaultGoods ) ) BEGIN SET @F_TEMPNODOAMOUNT = @F_TEMPNODOAMOUNT + 1; SET @F_TEMPOVERAMOUNT = @F_TEMPOVERAMOUNT + 1; END; ELSE BEGIN SET @F_TEMPCheckIn = @F_TEMPCheckIn + 1; IF ( @enddatetime IS NOT NULL ) BEGIN SET @F_TEMPOVERAMOUNT = @F_TEMPOVERAMOUNT + 1; END; END; IF ( @WorkerId IN ( '男', '女', '随意' ) ) BEGIN SET @F_TEMTTOTALVACANCY = @F_TEMTTOTALVACANCY + 1; IF ( @WorkerId = '女' ) BEGIN SET @F_TEMTFEMALEVACANCY = @F_TEMTFEMALEVACANCY + 1; END; ELSE IF ( @WorkerId = '男' ) BEGIN SET @F_TEMTMALEVACANCY = @F_TEMTMALEVACANCY + 1; END; END; IF ( @enddatetime IS NOT NULL ) BEGIN IF ( @startdatetime IS NULL ) BEGIN SET @starttime = CAST(GETDATE() AS TIME); END; ELSE BEGIN SET @starttime = CAST(@startdatetime AS TIME); END; SET @endtime = CAST(@enddatetime AS TIME); IF ( @starttime > @endtime ) BEGIN SET @costtime = ( DATEDIFF(SECOND, CAST('00:00:00' AS TIME), @endtime) + DATEDIFF(SECOND, @starttime, CAST('23:59:59' AS TIME)) ) / 60; END; ELSE BEGIN SET @costtime = ( DATEDIFF(SECOND, @startdatetime, @enddatetime) ) / 60; END; IF ( @amount < 0 ) SET @costtime = -@costtime; END; ELSE BEGIN IF ( @startdatetime IS NULL ) BEGIN SET @starttime = CAST(GETDATE() AS TIME); END; ELSE BEGIN SET @starttime = CAST(@startdatetime AS TIME); END; SELECT TOP 1 @costtime = F_STARTTIMES , @GoogTimeAmount = 1 FROM T_GOODSTIME WHERE ( ( ( @starttime BETWEEN F_STARPERIODTTIME AND F_ENDPERIODTIME ) AND F_STARPERIODTTIME < F_ENDPERIODTIME ) OR ( ( ( @starttime BETWEEN F_STARPERIODTTIME AND '23:59:59' ) OR ( @starttime BETWEEN '00:00:00' AND F_ENDPERIODTIME ) ) AND F_STARPERIODTTIME > F_ENDPERIODTIME ) ) AND F_GOODSID = @goodsid; IF ( @GoogTimeAmount = 1 ) BEGIN SET @costtime = @costtime * @amount; END; ELSE BEGIN SELECT @costtime = F_STARTTIMES FROM T_GOODS WHERE F_ID = @goodsid; SET @costtime = @costtime * @amount; END; END; --计算剩余时间 IF ( ( @startdatetime IS NULL ) AND ( @enddatetime IS NULL ) ) BEGIN SET @costtimesum = @costtime; END; IF ( ( @startdatetime IS NOT NULL ) AND ( @enddatetime IS NULL ) ) BEGIN IF ( @startdatetime <= @currdatetime ) BEGIN SET @costtimesum = @costtime - DATEDIFF(MINUTE, @startdatetime, @currdatetime) + @delay; END; ELSE BEGIN SET @costtimesum = @costtime + DATEDIFF(MINUTE, @startdatetime, @currdatetime) + @delay; END; END; IF ( @enddatetime IS NULL ) BEGIN IF ( ( @costtimesum > @F_TEMPCOSTTIME ) OR ( @F_TEMPCOSTTIME = 0 ) ) SET @F_TEMPCOSTTIME = @costtimesum; END; IF ( @enddatetime IS NOT NULL ) BEGIN IF ( ( @enddatetime > @F_TEMPENDDATETIME ) OR ( @F_TEMPENDDATETIME IS NULL ) ) SET @F_TEMPENDDATETIME = @enddatetime; END; END; IF ( @PRIORCHECKOUTID IS NOT NULL ) BEGIN SET @F_TEMPISPRIORCHECKOUT = 1; END; IF ( @ISSTOPAUTOTEA IS NOT NULL ) BEGIN SET @F_TEMPISREMINDSTOP = @ISSTOPAUTOTEA; END; FETCH NEXT FROM @CONSUMENOTCHECKOUT INTO @goodsid, @amount, @enterdatetime, @startdatetime, @enddatetime, @delay, @WorkerId, @PRIORCHECKOUTID, @ISSTOPAUTOTEA, @STARTTIMES, @GOODSTYPE1ID; END; CLOSE @CONSUMENOTCHECKOUT; DEALLOCATE @CONSUMENOTCHECKOUT; IF (@F_TEMPSTATE = '占用') BEGIN SET @ConsumeCount = 0; SET @ReceiveCount = 0; select @ReceiveCount = COUNT(1) from T_RECEIVE WHERE F_SHOPID = @shopid AND F_ROOMID = @F_TEMPID AND F_CHECKOUTID IS NULL; select @ConsumeCount = COUNT(1) from V_CONSUMENOTCHECKOUT WHERE F_SHOPID = @shopid AND F_ROOMID = @F_TEMPID; IF(@ConsumeCount = 0) AND (@ReceiveCount = 0) BEGIN SET @F_TEMPSTATE = '空闲'; END; END; --查询出来的数据插入返回表中 INSERT @RoomInfo VALUES ( @F_TEMPID, @F_TEMPROOMTYPEID, @F_TEMPSTATE, @F_TEMPAMOUNT, @F_TEMPCHECKINAMOUNT, @F_TEMPREMARK, @F_TEMPSHOPID, @F_TEMPFLOORAUTOID, @F_TEMPSHOWID, @F_TEMPISCHARGE, @F_TEMPGOODSID, @F_TEMPISTAKE, @F_TEMPWORKERID, @F_TEMPDESTINEDATETIME, @F_TEMPHOMOPHONY, @F_TEMPISLEAST, @F_TEMPLEAST, @F_TEMPNODOAMOUNT, @F_TEMPCheckIn, @F_TEMPOVERAMOUNT, @F_TEMPCOSTTIME, @F_TEMTTOTALVACANCY, @F_TEMTFEMALEVACANCY, @F_TEMTMALEVACANCY, @F_TEMPISAUDIOMUTING, @F_TEMPSTATEPLUS, @F_TEMPISCHECKOUTALL, @F_TEMPISPRIORCHECKOUT, @F_TEMPISREMINDSTOP, @F_TEMPENDDATETIME ); FETCH NEXT FROM @ROOMCURSOR INTO @F_TEMPID, @F_TEMPROOMTYPEID, @F_TEMPSTATE, @F_TEMPAMOUNT, @F_TEMPCHECKINAMOUNT, @F_TEMPREMARK, @F_TEMPSHOPID, @F_TEMPFLOORAUTOID, @F_TEMPSHOWID, @F_TEMPISCHARGE, @F_TEMPGOODSID, @F_TEMPISTAKE, @F_TEMPWORKERID, @F_TEMPDESTINEDATETIME, @F_TEMPHOMOPHONY, @F_TEMPISLEAST, @F_TEMPLEAST, @F_TEMPISAUDIOMUTING, @F_TEMPSTATEPLUS, @F_TEMPISCHECKOUTALL; END; CLOSE @ROOMCURSOR; DEALLOCATE @ROOMCURSOR; RETURN; END; GO