IF OBJECT_ID (N'T_PROPAYMENTLIST') IS NULL BEGIN CREATE TABLE [dbo].[T_PROPAYMENTLIST]( [F_ID] [varchar](20) NOT NULL, [F_PAYID] [varchar](20) NOT NULL, [F_TYPE] [smallint] NOT NULL, [F_CLIENTID] [varchar](50) NULL, [F_TICKETTYPE] [smallint] NULL, [F_MONEY] [decimal](9, 2) NULL, [F_RECEIVEID] [varchar](20) NULL, [F_CONSUMEID] [varchar](20) NULL, [F_REBATE] [decimal](9, 2) NULL, [F_ONLINECLIENTID] [varchar](20) NULL, [F_ISREADCARD] [smallint] NULL, [F_OPENID] [varchar](50) NULL, [F_ISTIME] [smallint] NULL, [F_DATETIME] [datetime] NULL, [F_SHOPID] [varchar](20) NOT NULL, [F_TIMEGOODSID] [varchar](20) NULL, [F_TIMEGOODSCOUNT] [decimal](9, 2) NULL, [F_REMARK] [varchar](100) NULL, [F_ONLINEPAYID] [varchar](100) NULL, [F_NAME] [varchar](50) NULL, [F_TICKETID] [varchar](50) NULL, [F_ONLINEPAYTYPE] [smallint] NULL, [F_KEYID] [varchar](20) NULL, [F_VALUE] [decimal](9, 2) NULL, [F_TICKETCONSUMETYPE] [smallint] NULL, [F_CLIENTCANCOSTMONEY] [decimal](9, 2) NULL, [F_CLIENTACTUALREBATE] [decimal](9, 2) NULL, [F_TICKETTYPEID] [varchar](50) NULL, CONSTRAINT [PK_T_PROPAYMENTLIST] 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_SOEPAYPRO') IS NULL BEGIN CREATE TABLE [dbo].[T_SOEPAYPRO]( [F_ID] [VARCHAR](20) NOT NULL, [F_TYPE] [SMALLINT] NOT NULL, [F_DATETIME] [DATETIME] NULL, [F_PAYCOMPANY] [VARCHAR](20) NULL, [F_MONEY] [MONEY] NULL, [F_PAYTIME] [DATETIME] NULL, [F_STATE] [VARCHAR](20) NULL, [F_KEYID] [VARCHAR](20) NULL, [F_PAYTYPE] [VARCHAR](20) NULL, [F_REFUNDDATETIME] [DATETIME] NULL, [F_REFUNDID] [VARCHAR](50) NULL, [F_REFUNDMONEY] [MONEY] NULL, [F_REFUNDREMARK] [VARCHAR](50) NULL, [F_REMARK] [VARCHAR](200) NULL, [F_SHOPID] [VARCHAR](10) NOT NULL, [F_ORDERID] [VARCHAR](30) NULL, [F_LASTUPDATETIME] [DATETIME] NULL, [F_USERID] [VARCHAR](20) NULL, [F_PAYID] [VARCHAR](20) NULL, [F_QRCODE] [VARCHAR](200) NULL, CONSTRAINT [PK_T_SOEPAYPRO] 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 NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_RECEIVE') and Name='F_PAYID') BEGIN ALTER TABLE T_RECEIVE ADD F_PAYID VARCHAR(20) NULL END GO IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'V_CLIENTPRO') BEGIN DROP View V_CLIENTPRO; END GO CREATE VIEW [dbo].[V_CLIENTPRO] AS SELECT T_CLIENT1.F_AUTOID, T_CLIENT1.F_ID, T_CLIENT1.F_NAME, T_CLIENT1.F_SEX, T_CLIENT1.F_TELEPHONE, T_CLIENT1.F_ADDRESS, T_CLIENT1.F_CANCOSTMONEY, T_CLIENT1.F_REBATE, T_CLIENT1.F_REMARK, T_CLIENT1.F_PASSWORD, T_CLIENT1.F_DATE, T_CLIENT1.F_BORNDATE, T_CLIENT1.F_GOODSREBATE, T_CLIENT1.F_WORKERID, T_CLIENT1.F_CARDTYPEID, dbo.T_CARDTYPE.F_NAME AS F_CARDTYPENAME, T_CLIENT1.F_SERVEREBATE, T_CLIENT1.F_INTEGRAL, T_CLIENT1.F_STARTDATE, T_CLIENT1.F_ENDDATE, T_CLIENT1.F_FIRSTDATE, T_CLIENT1.F_LASTDATE, T_CLIENT1.F_STATE, T_CLIENT1.F_MOVETEL, T_CLIENT1.F_SHOPID, T_CLIENT1.F_LUNARYBORNDATE, T_CLIENT1.F_LASTVISITCONNECT, T_CLIENT1.F_ARREARAGEMONEY, T_CLIENT1.F_GOODSREBATE1, T_CLIENT1.F_SERVEREBATE1, T_CLIENT1.F_SERVEREBATE2, T_CLIENT1.F_SERVEREBATE3, T_CLIENT1.F_ISSMSAWOKE, T_CLIENT1.F_SUPPLEMENTTOTALMONEY, T_CLIENT1.F_SUPPLEMENTTOTALTIME, T_CLIENT1.F_CONSUMETOTALMONEY, T_CLIENT1.F_CREDITMONEY, T_CLIENT1.F_CANDEBTMONEY, T_CLIENT1.F_INTERNALCARDID, dbo.T_CARDTYPE.F_ISVIPPRICE, dbo.T_CARDTYPE.F_CANGOODSTYPE AS F_CANGODDSTYPE, RIGHT(CONVERT(VARCHAR, T_CLIENT1.F_BORNDATE, 112), 4) AS F_BORNDATEMMDD, RIGHT(CONVERT(VARCHAR, T_CLIENT1.F_LUNARYBORNDATE, 112), 4) AS F_LUNARYBORNDATEMMDD, T_CLIENT1.F_LASTSUPPLEMENTDATETIME, T_CLIENT1.F_LIMITMONEY, T_CLIENT1.F_IsAllowWeiXinCheckConsume, T_CLIENT1.F_PRESENTMONEY, dbo.T_CARDTYPE.F_ISVIPPRICEBYNOCASH, T_CLIENT1.F_CONSUMETOTALTIME FROM dbo.T_CLIENT AS T_CLIENT1 LEFT OUTER JOIN dbo.T_CARDTYPE ON T_CLIENT1.F_CARDTYPEID = dbo.T_CARDTYPE.F_ID GO IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'V_TICKET1') BEGIN DROP View V_TICKET1; END GO CREATE VIEW [dbo].[V_TICKET1] AS SELECT dbo.T_TICKETTYPE.F_NAME, dbo.T_TICKET.F_STARTDATE, dbo.T_TICKET.F_ENDDATE, dbo.T_TICKET.F_ID, dbo.T_TICKET.F_STATE, dbo.T_TICKET.F_SNID,dbo.T_TICKET.F_TYPE, dbo.T_TICKETTYPE.F_FACTORTYPE, dbo.T_TICKETTYPE.F_ISCHECKSHOPID, dbo.T_TICKETTYPE.F_ISMONEYCANREBATE, dbo.T_TICKETTYPE.F_CONSUMETYPE, dbo.T_TICKETTYPE.F_MONEYREBATETYPE, dbo.T_TICKETTYPE.F_ISGIFTVOUCHER, dbo.T_TICKETTYPE.F_GoodsType1ID, dbo.T_TICKETTYPE.F_IsVirtualCard, dbo.T_TICKETTYPE.F_FollowAdd, dbo.T_TICKET.F_CLIENTID, dbo.T_TICKET.F_PHONE, dbo.T_TICKET.F_SHOPID, dbo.T_TICKETTYPE.F_COSTTYPE, dbo.T_TICKETTYPE.F_MONEY, dbo.T_TICKETTYPE.F_SelfApply, dbo.T_TICKETTYPE.F_OnceMore, dbo.T_TICKETTYPE.F_GivingCount, dbo.T_TICKETTYPE.F_LimitedCount, dbo.T_TICKETTYPE.F_AskForStartDate, dbo.T_TICKETTYPE.F_AskForEndDate, dbo.T_TICKETTYPE.F_UseStartDate, dbo.T_TICKETTYPE.F_UseDayCount, dbo.T_TICKETTYPE.F_UseEndDate, dbo.T_TICKETTYPE.F_StyleName FROM dbo.T_TICKET INNER JOIN dbo.T_TICKETTYPE ON dbo.T_TICKET.F_TYPE = dbo.T_TICKETTYPE.F_ID GO if not exists(select * from T_WORKERQUEUEPARAMETER where F_ID = 'EnableNewCheckout') BEGIN INSERT INTO T_WORKERQUEUEPARAMETER (F_ID, F_VALUE, F_SHOPID, F_REMARK, F_ISHIDE, F_TYPE,F_VERSION,F_ADDDATE) VALUES ('EnableNewCheckout', '0', NULL, '启用新结账逻辑', 0, '系统','18.1.126','2018-03-22'); END GO IF NOT EXISTS( SELECT * FROM T_CHECKOUTTYPE WHERE F_NAME = '微信支付') BEGIN INSERT T_CHECKOUTTYPE ( F_ID , F_NAME , F_OrderID , F_IsStop , F_IsSupplent , F_IsCheckout , F_IsNeedAuthorize ) VALUES ( 'W20' , -- 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 NOT EXISTS( SELECT * FROM T_CHECKOUTTYPE WHERE F_NAME = '支付宝支付') BEGIN INSERT T_CHECKOUTTYPE ( F_ID , F_NAME , F_OrderID , F_IsStop , F_IsSupplent , F_IsCheckout , F_IsNeedAuthorize ) VALUES ( 'W21' , -- 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 not exists(select * from T_WORKERQUEUEPARAMETER where F_ID = 'IsNewSoePayPlayVoice') BEGIN INSERT INTO T_WORKERQUEUEPARAMETER (F_ID, F_VALUE, F_SHOPID, F_REMARK, F_ISHIDE, F_TYPE,F_VERSION,F_ADDDATE) VALUES ('IsNewSoePayPlayVoice', '0', NULL, '开启新结账微支付收款语音', 0, '系统','18.1.126','2018-05-06'); END GO IF NOT EXISTS (SELECT * FROM T_POPEDOM WHERE F_ID = 5062) INSERT T_POPEDOM (F_ID,F_NAME,F_VALUE) VALUES(5062,'手牌发牌/回收',304) ; GO ALTER 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, dbo.T_ROOM.F_ID AS F_ROOMID, dbo.T_ROOM.F_SHOWID AS F_ROOMSHOWID, dbo.T_HANDBRAND.F_ID AS F_HANDBRANDID, dbo.T_HANDBRAND.F_SHOWID AS F_HANDBRANDSHOWID, T_GOODS_1.F_GOODSTYPE1ID, T_GOODS_1.F_STARTTIMES, dbo.T_RECEIVE.F_CHECKOUTID, dbo.T_CHECKOUT.F_DATETIME AS F_CHECKOUTDATETIME FROM dbo.T_CONSUME LEFT OUTER 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_CHECKOUT ON dbo.T_RECEIVE.F_CHECKOUTID = dbo.T_CHECKOUT.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 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 ORDER BY dbo.T_CONSUME.F_MINISTERID GO if not exists(select * from T_WORKERQUEUEPARAMETER where F_ID = 'IsNeedMinisterAfterHandBrand') BEGIN INSERT INTO T_WORKERQUEUEPARAMETER (F_ID, F_VALUE, F_SHOPID, F_REMARK, F_ISHIDE, F_TYPE,F_VERSION,F_ADDDATE) VALUES ('IsNeedMinisterAfterHandBrand', '0', NULL, '浴场房间手牌开单是否需要刷领班卡', 0, '系统','18.1.126','2018-05-06'); END GO IF EXISTS (SELECT * FROM T_POPEDOM WHERE F_ID='3300' ) BEGIN UPDATE dbo.T_POPEDOM SET F_IsTempPopedom=1 WHERE F_ID='3300'; END GO ALTER VIEW [dbo].[V_GRATISTCONSUMEByCheckout] AS SELECT dbo.T_GRATISTCONSUME.F_ID, dbo.T_CHECKOUT.F_DATETIME, dbo.T_GRATISTCONSUME.F_RECEIVEID, dbo.T_GRATISTCONSUME.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_GRATISTCONSUME.F_PRICE, dbo.T_GRATISTCONSUME.F_AMOUNT, dbo.T_GRATISTCONSUME.F_REMARK, dbo.T_RECEIVE.F_USERID, dbo.T_RECEIVE.F_SHOPID, dbo.T_GRATISTCONSUME.F_STATE, dbo.T_RECEIVE.F_HANDBRANDID, dbo.T_RECEIVE.F_ROOMID, dbo.T_TEABILL.F_STARTDATETIME, dbo.T_TEABILL.F_ENDDATETIME, dbo.T_TEABILL.F_WORKERID, dbo.T_WORKER.F_ID AS F_SHOWWORKERID, dbo.T_GRATISTCONSUME.F_STARTDATETIME AS F_ENTERDATETIME, dbo.T_GRATISTCONSUME.F_ROOMAREA, dbo.T_GOODS.F_SHOWID AS F_GOODSSHOWID, dbo.T_RECEIVE.F_CHECKOUTID FROM dbo.T_GRATISTCONSUME INNER JOIN dbo.T_TEABILL ON dbo.T_TEABILL.F_ID = dbo.T_GRATISTCONSUME.F_TEABILLID INNER JOIN dbo.T_GOODS ON dbo.T_GRATISTCONSUME.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 INNER JOIN dbo.T_RECEIVE ON dbo.T_GRATISTCONSUME.F_RECEIVEID = dbo.T_RECEIVE.F_ID INNER JOIN dbo.T_CHECKOUT ON dbo.T_RECEIVE.F_CHECKOUTID = dbo.T_CHECKOUT.F_ID LEFT OUTER JOIN dbo.T_WORKER ON dbo.T_GRATISTCONSUME.F_MINISTERID = dbo.T_WORKER.F_ARCHIVESID GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_ROOM') and Name='F_ISDIRTY') BEGIN ALTER TABLE T_ROOM ADD F_ISDIRTY INTEGER NULL END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_ROOM') and Name='F_CHECKOUTDATETIME') BEGIN ALTER TABLE T_ROOM ADD F_CHECKOUTDATETIME DATETIME NULL END GO IF EXISTS ( SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'GETROOMSTATEANDCOSTTIME') AND xtype IN ( 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, F_ISDIRTY INTEGER, F_CHECKOUTDATETIME 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; DECLARE @F_TEMPISDIRTY INTEGER; DECLARE @F_TEMPCHECKOUTDATETIME DATETIME; 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, F_ISDIRTY, F_CHECKOUTDATETIME 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, @F_TEMPISDIRTY, @F_TEMPCHECKOUTDATETIME; 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; IF (@F_TEMPISDIRTY = 1) BEGIN SET @F_TEMPSTATE = '已结账'; 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, @F_TEMPISDIRTY, @F_TEMPCHECKOUTDATETIME ); 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, @F_TEMPISDIRTY, @F_TEMPCHECKOUTDATETIME; END; CLOSE @ROOMCURSOR; DEALLOCATE @ROOMCURSOR; RETURN; END; GO IF OBJECT_ID (N'T_DISPOSE_CUSTOM') IS NULL BEGIN CREATE TABLE [dbo].[T_DISPOSE_CUSTOM]( [F_ID] [VARCHAR](20) NOT NULL, [F_WORKERID] [VARCHAR](10) NOT NULL, [F_DATETIME] [DATE] NOT NULL, [F_SCHEDULEID] [VARCHAR](10) NULL, [F_STARTTIME] [TIME](7) NULL, [F_ENDTIME] [TIME](7) NULL, [F_SHOPID] [VARCHAR](10) NULL, [F_REMARK] [VARCHAR](200) NULL, [F_CONTROLDATETIME] [DATETIME] NOT NULL, CONSTRAINT [PK_T_DISPOSE_CUSTOM] 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_RECEIVE') and Name='F_ORIGINALROOMID') BEGIN ALTER TABLE T_RECEIVE ADD F_ORIGINALROOMID VARCHAR(20) NULL END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_RECEIVE_H') and Name='F_PAYID') BEGIN ALTER TABLE T_RECEIVE_H ADD F_PAYID VARCHAR(20) NULL END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_RECEIVE_H') and Name='F_ORIGINALROOMID') BEGIN ALTER TABLE T_RECEIVE_H ADD F_ORIGINALROOMID VARCHAR(20) NULL END GO if not exists(select * from T_WORKERQUEUEPARAMETER where F_ID = 'IsDestineIDMulti') BEGIN INSERT INTO T_WORKERQUEUEPARAMETER (F_ID, F_VALUE, F_SHOPID, F_REMARK, F_ISHIDE, F_TYPE,F_VERSION,F_ADDDATE ) VALUES ('IsDestineIDMulti', 0, null, '预约多个员工是否生成独立的预约号', 0, '基础设置','18.0.126.1528','2018-06-05'); END GO IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'V_DISPOSE_CUSTOM') BEGIN DROP View V_DISPOSE_CUSTOM; END GO CREATE VIEW [dbo].[V_DISPOSE_CUSTOM] AS SELECT dbo.T_SCHEDULE.F_TIMEOFF, dbo.T_SCHEDULE.F_TIME, dbo.T_DISPOSE_CUSTOM.F_ID, dbo.T_DISPOSE_CUSTOM.F_WORKERID, dbo.T_DISPOSE_CUSTOM.F_DATETIME, dbo.T_DISPOSE_CUSTOM.F_SCHEDULEID, dbo.T_DISPOSE_CUSTOM.F_STARTTIME, dbo.T_DISPOSE_CUSTOM.F_ENDTIME, dbo.T_DISPOSE_CUSTOM.F_SHOPID, dbo.T_DISPOSE_CUSTOM.F_REMARK, dbo.T_DISPOSE_CUSTOM.F_CONTROLDATETIME, dbo.T_SCHEDULE.F_NAME AS F_SCHEDULENAME, dbo.T_WORKER.F_NAME AS F_WORKERNAME FROM dbo.T_DISPOSE_CUSTOM INNER JOIN dbo.T_SCHEDULE ON dbo.T_DISPOSE_CUSTOM.F_SCHEDULEID = dbo.T_SCHEDULE.F_ID INNER JOIN dbo.T_WORKER ON dbo.T_DISPOSE_CUSTOM.F_WorkerID = dbo.T_WORKER.F_ARCHIVESID GO IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'V_GRATISTCONSUME') BEGIN DROP View V_GRATISTCONSUME; END 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, T_GRATISTCONSUME.F_MINISTERID, T_WORKER.F_ID AS F_MINISTERSHOWID, T_WORKER.F_NAME AS F_MINISTERNAME, T_HANDBRAND.F_SHOWID AS F_HANDBRANDSHOWID, T_ROOM.F_SHOWID AS F_ROOMSHOWID 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 LEFT OUTER JOIN T_HANDBRAND ON T_RECEIVE.F_HANDBRANDID = T_HANDBRAND.F_ID LEFT OUTER JOIN T_ROOM ON T_RECEIVE.F_ROOMID = T_ROOM.F_ID; GO if not exists(select * from T_WORKERQUEUEPARAMETER where F_ID = 'IsSmartChangeRoom') BEGIN INSERT INTO T_WORKERQUEUEPARAMETER (F_ID, F_VALUE, F_SHOPID, F_REMARK, F_ISHIDE, F_TYPE,F_VERSION,F_ADDDATE ) VALUES ('IsSmartChangeRoom', 1, null, '启用智钟宝转房', 0, '基础设置','18.1.126.1528','2018-06-09'); END GO IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'V_ReceiveNotCheckout') BEGIN DROP View V_ReceiveNotCheckout; END GO CREATE VIEW [dbo].[V_ReceiveNotCheckout] AS SELECT dbo.T_RECEIVE.F_ID, (case when SUM(dbo.T_CONSUME.F_GATHERINGSUM) is NULL then '0' else SUM(dbo.T_CONSUME.F_GATHERINGSUM) end) as F_GATHERINGSUM, F_DATETIME, T_ROOM.F_SHOWID as F_ROOMShowID, T_HandBrand.F_SHOWID as F_HANDBRANDShowID, T_RECEIVE.F_SHOPID, F_JOINID FROM dbo.T_RECEIVE left JOIN dbo.T_CONSUME ON dbo.T_RECEIVE.F_ID = dbo.T_CONSUME.F_RECEIVEID left JOIN dbo.T_Room ON dbo.T_RECEIVE.F_RoomID = dbo.T_Room.F_ID left JOIN dbo.T_HANDBRAND ON dbo.T_RECEIVE.F_HANDBRANDID = dbo.T_HANDBRAND.F_ID where T_RECEIVE.F_CHECKOUTID is null group by T_RECEIVE.F_ID, F_DATETIME, T_ROOM.F_ShowID, T_HandBrand.F_ShowID, T_RECEIVE.F_SHOPID, F_JOINID GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_CONSUME') and Name='F_SOURCERECEIVEID') BEGIN ALTER TABLE T_CONSUME ADD F_SOURCERECEIVEID VARCHAR(50) NULL END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_CONSUMEWORKER') and Name='F_SOURCERECEIVEID') BEGIN ALTER TABLE T_CONSUMEWORKER ADD F_SOURCERECEIVEID VARCHAR(50) NULL END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_PROPAYMENTLIST') and Name='F_PRIORCHECKOUTID') BEGIN ALTER TABLE T_PROPAYMENTLIST ADD F_PRIORCHECKOUTID VARCHAR(50) NULL END GO ALTER 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, R1.F_ID AS F_ROOMID, R1.F_SHOWID AS F_ROOMSHOWID,R2.F_SHOWID AS F_ORIGINALROOMSHOWID, dbo.T_HANDBRAND.F_ID AS F_HANDBRANDID, dbo.T_HANDBRAND.F_SHOWID AS F_HANDBRANDSHOWID, T_GOODS_1.F_GOODSTYPE1ID, T_GOODS_1.F_STARTTIMES, dbo.T_RECEIVE.F_CHECKOUTID, dbo.T_CHECKOUT.F_DATETIME AS F_CHECKOUTDATETIME FROM dbo.T_CONSUME LEFT OUTER 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_CHECKOUT ON dbo.T_RECEIVE.F_CHECKOUTID = dbo.T_CHECKOUT.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 LEFT OUTER JOIN dbo.T_ROOM R1 ON dbo.T_RECEIVE.F_ROOMID = R1.F_ID LEFT OUTER JOIN dbo.T_ROOM R2 ON dbo.T_RECEIVE.F_ORIGINALROOMID = R2.F_ID LEFT OUTER JOIN dbo.T_HANDBRAND ON dbo.T_RECEIVE.F_HANDBRANDID = dbo.T_HANDBRAND.F_ID ORDER BY dbo.T_CONSUME.F_MINISTERID GO ALTER VIEW [dbo].[V_GRATISTCONSUME] AS SELECT dbo.T_GRATISTCONSUME.F_ID, dbo.T_RECEIVE.F_DATETIME AS F_RECEIVEDATETIME, dbo.T_GRATISTCONSUME.F_RECEIVEID, dbo.T_GRATISTCONSUME.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_GRATISTCONSUME.F_PRICE, dbo.T_GRATISTCONSUME.F_AMOUNT, dbo.T_GRATISTCONSUME.F_REMARK, dbo.T_RECEIVE.F_USERID AS F_RECEIVEUSERID, dbo.T_RECEIVE.F_SHOPID, dbo.T_GRATISTCONSUME.F_STATE, dbo.T_RECEIVE.F_HANDBRANDID, dbo.T_RECEIVE.F_ROOMID, dbo.T_TEABILL.F_STARTDATETIME, dbo.T_TEABILL.F_ENDDATETIME, dbo.T_TEABILL.F_WORKERID, dbo.T_WORKER.F_ID AS F_SHOWWORKERID, dbo.T_RECEIVE.F_CHECKOUTID, dbo.T_GRATISTCONSUME.F_STARTDATETIME AS F_ENTERDATETIME, dbo.T_CHECKOUT.F_DATETIME, dbo.T_GRATISTCONSUME.F_ROOMAREA, dbo.T_CHECKOUT.F_USERID, dbo.T_GRATISTCONSUME.F_MINISTERID, dbo.T_WORKER.F_ID AS F_MINISTERSHOWID, dbo.T_WORKER.F_NAME AS F_MINISTERNAME, dbo.T_HANDBRAND.F_SHOWID AS F_HANDBRANDSHOWID, dbo.T_ROOM.F_SHOWID AS F_ROOMSHOWID, dbo.T_GRATISTCONSUME.F_TEABILLID, dbo.T_GOODS.F_SHOWID AS F_GOODSSHOWID FROM dbo.T_GRATISTCONSUME INNER JOIN dbo.T_TEABILL ON dbo.T_TEABILL.F_ID = dbo.T_GRATISTCONSUME.F_TEABILLID INNER JOIN dbo.T_GOODS ON dbo.T_GRATISTCONSUME.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 INNER JOIN dbo.T_RECEIVE ON dbo.T_GRATISTCONSUME.F_RECEIVEID = dbo.T_RECEIVE.F_ID LEFT OUTER JOIN dbo.T_CHECKOUT ON dbo.T_RECEIVE.F_CHECKOUTID = dbo.T_CHECKOUT.F_ID LEFT OUTER JOIN dbo.T_WORKER ON dbo.T_GRATISTCONSUME.F_MINISTERID = dbo.T_WORKER.F_ARCHIVESID 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 GO ALTER 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, T_HEADSHIP1.F_TYPE AS F_CONSUMEWORKERHEADTYPE, T_GOODS.F_ISSMALL AS F_ISSMALL, T_GOODS.F_FALLTIME AS F_FALLTIME, T_WORKER.F_STATE, V_CHECKOUT.F_USERID AS F_CHECKOUTUSERID 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 ALTER VIEW [dbo].[V_MINISTERANALYSIS] AS SELECT TOP ( 100 ) PERCENT T_CONSUME.F_RECEIVEID , T_CONSUME.F_PRICE , T_CONSUME.F_AMOUNT , T_CONSUME.F_ISPRESENT , T_CONSUME.F_CANREBATE , T_CONSUME.F_REBATE , T_CONSUME.F_USERID , T_CONSUME.F_COST , T_CONSUME.F_STARTDATETIME , T_CONSUME.F_ENDDATETIME , T_CONSUME.F_ISPROMOTION , T_CONSUME.F_ISTOGO , T_CONSUME.F_ISTIME , T_CONSUME.F_OLDPRICE , T_CONSUME.F_CLIENTREBATE , T_CONSUME.F_CLIENTGOODSREBATE , T_CONSUME.F_ENTERDATETIME , T_CONSUME.F_CLOCKINFO , T_CONSUME.F_MINISTERGOODSID , T_CONSUME.F_ROOMAREA , T_CONSUME.F_SEATNUMBER , T_CONSUME.F_HEADSHIPLEVEL , T_CONSUME.F_GOODSPLUSPARENTID , T_CONSUME.F_GATHERINGSUM , T_CONSUME.F_WORKERDEDUCT , T_CONSUMEWORKER.F_ISNAMED , T_CONSUMEWORKER.F_SelClock , T_CONSUMEWORKER.F_ISADDCLOCK , T_CONSUMEWORKER.F_HEADSHIPID AS F_WORKERHEADSHIPID , T_WORKER_1.F_ID AS F_MINISTERSHOWID , T_WORKER_1.F_NAME AS F_MINISTERNAME , T_WORKER.F_NAME AS F_WORKERNAME , T_WORKER.F_ID AS F_WORKERSHOWID , T_HEADSHIP.F_NAME AS F_WORKERHEADSHIPNAME , T_WORKER_1.F_SEX AS F_MINISTERSEX , T_WORKER.F_SEX AS F_WORKERSEX , T_GOODS_1.F_NAME AS F_GOODSNAME , T_GOODS_1.F_SHOWID AS F_GOODSSHOWID , T_RECEIVE.F_DATETIME AS F_RECEIVEDATETIME , T_CONSUME.F_MINISTERID , T_GOODS.F_NAME AS F_MINISTERGOODSNAME , T_GOODS.F_SHOWID AS F_MINISTERGOODSSHOWID , T_RECEIVE.F_SHOPID , R1.F_ID AS F_ROOMID , R1.F_SHOWID AS F_ROOMSHOWID , R2.F_SHOWID AS F_ORIGINALROOMSHOWID , T_HANDBRAND.F_ID AS F_HANDBRANDID , T_HANDBRAND.F_SHOWID AS F_HANDBRANDSHOWID , T_GOODS_1.F_GOODSTYPE1ID , T_GOODS_1.F_STARTTIMES , T_RECEIVE.F_CHECKOUTID , T_CHECKOUT.F_DATETIME AS F_CHECKOUTDATETIME FROM T_CONSUME LEFT OUTER JOIN T_CONSUMEWORKER ON (T_CONSUME.F_ID = T_CONSUMEWORKER.F_CONSUMEID AND T_CONSUMEWORKER.F_HEADSHIPID NOT IN (SELECT F_ID FROM T_HEADSHIP WHERE F_TYPE = 1)) 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 LEFT OUTER JOIN T_GOODS ON T_CONSUME.F_MINISTERGOODSID = T_GOODS.F_ID LEFT OUTER JOIN T_GOODS AS T_GOODS_1 ON T_CONSUME.F_GOODSID = T_GOODS_1.F_ID LEFT OUTER JOIN T_HEADSHIP ON T_CONSUMEWORKER.F_HEADSHIPID = T_HEADSHIP.F_ID LEFT OUTER JOIN T_WORKER AS T_WORKER_1 ON T_CONSUME.F_MINISTERID = T_WORKER_1.F_ARCHIVESID LEFT OUTER JOIN T_WORKER ON T_CONSUMEWORKER.F_WORKERID = T_WORKER.F_ARCHIVESID LEFT OUTER JOIN T_ROOM R1 ON T_RECEIVE.F_ROOMID = R1.F_ID LEFT OUTER JOIN T_ROOM R2 ON T_RECEIVE.F_ORIGINALROOMID = R2.F_ID LEFT OUTER JOIN T_HANDBRAND ON T_RECEIVE.F_HANDBRANDID = T_HANDBRAND.F_ID ORDER BY T_CONSUME.F_MINISTERID GO if not exists(select * from T_WORKERQUEUEPARAMETER where F_ID = 'CustomSchedeTimeForEarlyWork') BEGIN INSERT INTO T_WORKERQUEUEPARAMETER (F_ID, F_VALUE, F_SHOPID, F_REMARK, F_ISHIDE, F_TYPE,F_VERSION,F_ADDDATE) VALUES ('CustomSchedeTimeForEarlyWork', '', NULL, '提前上班不算加班时间点（自定义班次）', 0, '系统','18.1.126','2018-06-16'); END GO ALTER VIEW [dbo].[V_DISPOSE_CUSTOM] AS SELECT dbo.T_SCHEDULE.F_TIMEOFF, dbo.T_SCHEDULE.F_TIME, dbo.T_DISPOSE_CUSTOM.F_ID, dbo.T_DISPOSE_CUSTOM.F_WORKERID, dbo.T_DISPOSE_CUSTOM.F_DATETIME, dbo.T_DISPOSE_CUSTOM.F_SCHEDULEID, dbo.T_DISPOSE_CUSTOM.F_STARTTIME, dbo.T_DISPOSE_CUSTOM.F_ENDTIME, dbo.T_DISPOSE_CUSTOM.F_SHOPID, dbo.T_DISPOSE_CUSTOM.F_REMARK, dbo.T_DISPOSE_CUSTOM.F_CONTROLDATETIME, dbo.T_SCHEDULE.F_NAME AS F_SCHEDULENAME, dbo.T_WORKER.F_NAME AS F_WORKERNAME, dbo.T_WORKER.F_ID AS F_WORKERSHOWID FROM dbo.T_DISPOSE_CUSTOM INNER JOIN dbo.T_SCHEDULE ON dbo.T_DISPOSE_CUSTOM.F_SCHEDULEID = dbo.T_SCHEDULE.F_ID INNER JOIN dbo.T_WORKER ON dbo.T_DISPOSE_CUSTOM.F_WORKERID = dbo.T_WORKER.F_ARCHIVESID GO ALTER VIEW [dbo].[V_MINISTERANALYSIS] AS SELECT TOP ( 100 ) PERCENT T_CONSUME.F_RECEIVEID , T_CONSUME.F_PRICE , T_CONSUME.F_AMOUNT , T_CONSUME.F_ISPRESENT , T_CONSUME.F_CANREBATE , T_CONSUME.F_REBATE , T_CONSUME.F_USERID , T_CONSUME.F_COST , T_CONSUME.F_STARTDATETIME , T_CONSUME.F_ENDDATETIME , T_CONSUME.F_ISPROMOTION , T_CONSUME.F_ISTOGO , T_CONSUME.F_ISTIME , T_CONSUME.F_OLDPRICE , T_CONSUME.F_CLIENTREBATE , T_CONSUME.F_CLIENTGOODSREBATE , T_CONSUME.F_ENTERDATETIME , T_CONSUME.F_CLOCKINFO , T_CONSUME.F_MINISTERGOODSID , T_CONSUME.F_ROOMAREA , T_CONSUME.F_SEATNUMBER , T_CONSUME.F_HEADSHIPLEVEL , T_CONSUME.F_GOODSPLUSPARENTID , T_CONSUME.F_GATHERINGSUM , T_CONSUME.F_WORKERDEDUCT , T_CONSUMEWORKER.F_ISNAMED , T_CONSUMEWORKER.F_SelClock , T_CONSUMEWORKER.F_ISADDCLOCK , T_CONSUMEWORKER.F_HEADSHIPID AS F_WORKERHEADSHIPID , T_WORKER_1.F_ID AS F_MINISTERSHOWID , T_WORKER_1.F_NAME AS F_MINISTERNAME , T_WORKER.F_NAME AS F_WORKERNAME , T_WORKER.F_ID AS F_WORKERSHOWID , T_HEADSHIP.F_NAME AS F_WORKERHEADSHIPNAME , T_WORKER_1.F_SEX AS F_MINISTERSEX , T_WORKER.F_SEX AS F_WORKERSEX , T_GOODS_1.F_NAME AS F_GOODSNAME , T_GOODS_1.F_SHOWID AS F_GOODSSHOWID , T_RECEIVE.F_DATETIME AS F_RECEIVEDATETIME , T_CONSUME.F_MINISTERID , T_GOODS.F_NAME AS F_MINISTERGOODSNAME , T_GOODS.F_SHOWID AS F_MINISTERGOODSSHOWID , T_RECEIVE.F_SHOPID , R1.F_ID AS F_ROOMID , R1.F_SHOWID AS F_ROOMSHOWID , R2.F_SHOWID AS F_ORIGINALROOMSHOWID , T_HANDBRAND.F_ID AS F_HANDBRANDID , T_HANDBRAND.F_SHOWID AS F_HANDBRANDSHOWID , T_GOODS_1.F_GOODSTYPE1ID , T_GOODS_1.F_STARTTIMES , T_RECEIVE.F_CHECKOUTID , T_CHECKOUT.F_DATETIME AS F_CHECKOUTDATETIME, T_GOODSTYPE1.F_NAME AS F_GOODSTYPENAME FROM T_CONSUME LEFT OUTER JOIN T_CONSUMEWORKER ON (T_CONSUME.F_ID = T_CONSUMEWORKER.F_CONSUMEID AND T_CONSUMEWORKER.F_HEADSHIPID NOT IN (SELECT F_ID FROM T_HEADSHIP WHERE F_TYPE = 1)) 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 LEFT OUTER JOIN T_GOODS ON T_CONSUME.F_MINISTERGOODSID = T_GOODS.F_ID LEFT OUTER JOIN T_GOODS AS T_GOODS_1 ON T_CONSUME.F_GOODSID = T_GOODS_1.F_ID LEFT OUTER JOIN T_GOODSTYPE1 ON T_GOODSTYPE1.F_ID = T_GOODS_1.F_GOODSTYPE1ID LEFT OUTER JOIN T_HEADSHIP ON T_CONSUMEWORKER.F_HEADSHIPID = T_HEADSHIP.F_ID LEFT OUTER JOIN T_WORKER AS T_WORKER_1 ON T_CONSUME.F_MINISTERID = T_WORKER_1.F_ARCHIVESID LEFT OUTER JOIN T_WORKER ON T_CONSUMEWORKER.F_WORKERID = T_WORKER.F_ARCHIVESID LEFT OUTER JOIN T_ROOM R1 ON T_RECEIVE.F_ROOMID = R1.F_ID LEFT OUTER JOIN T_ROOM R2 ON T_RECEIVE.F_ORIGINALROOMID = R2.F_ID LEFT OUTER JOIN T_HANDBRAND ON T_RECEIVE.F_HANDBRANDID = T_HANDBRAND.F_ID ORDER BY T_CONSUME.F_MINISTERID GO ALTER VIEW [V_WORKERQUEUEDETAIL] AS SELECT T_CONSUMEWORKER.F_ID , T_CONSUMEWORKER.F_WORKERID , ( CASE WHEN T_CONSUMEWORKER.F_ISNAMED = 1 THEN '点钟' ELSE '排钟' END ) AS F_ISNAMED , T_HEADSHIP.F_NAME AS F_HEADSHIPNAME , V_CHECKOUT.F_ID AS F_CHECKOUTID , V_CHECKOUT.F_DATETIME AS F_CHECKOUTDATETIME , T_CONSUME.F_RECEIVEID , T_GOODS.F_SHOWID + '-' + T_GOODS.F_NAME AS F_GOODSNAME , T_GOODS.F_STARTTIMES , V_CHECKOUT.F_USERID , T_RECEIVE.F_SHOPID , T_CONSUME.F_STARTDATETIME , T_CONSUME.F_ENDDATETIME , T_RECEIVE.F_HANDBRANDID , COALESCE(T_RECEIVE.F_ROOMID, T_RECEIVE.F_ORIGINALROOMID) AS F_ROOMID, T_CONSUMEWORKER.F_OUTDATETIME , T_CONSUMEWORKER.F_BACKDATETIME , T_RECEIVE.F_DATETIME AS F_RECEIVEDATETIME , T_GOODS.F_FALLTIME , T_CONSUME.F_AMOUNT , T_CONSUME.F_MINISTERID , T_CONSUME.F_DELAY , T_CONSUME.F_GOODSID , T_WORKER.F_ID AS F_SHOWWORKERID FROM T_CONSUMEWORKER INNER JOIN T_CONSUME ON T_CONSUMEWORKER.F_CONSUMEID = T_CONSUME.F_ID INNER JOIN T_HEADSHIP ON T_CONSUMEWORKER.F_HEADSHIPID = T_HEADSHIP.F_ID INNER JOIN T_GOODS ON T_CONSUME.F_GOODSID = T_GOODS.F_ID INNER JOIN T_RECEIVE ON T_CONSUME.F_RECEIVEID = T_RECEIVE.F_ID LEFT OUTER JOIN V_CHECKOUT ON T_RECEIVE.F_CHECKOUTID = V_CHECKOUT.F_ID INNER JOIN T_WORKER ON T_CONSUMEWORKER.F_WORKERID = T_WORKER.F_ARCHIVESID GO UPDATE dbo.T_POPEDOM SET F_IsTempPopedom=1 WHERE F_ID='650'; GO UPDATE dbo.T_POPEDOM SET F_IsTempPopedom=1 WHERE F_ID='5652'; GO ALTER 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, dbo.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, dbo.T_ROOM.F_ROOMTYPEID, dbo.T_CONSUME.F_NOTSTARTISFREEAMOUNT 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 EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_PROPAYMENTLIST') and Name='F_KEYID') BEGIN ALTER TABLE T_PROPAYMENTLIST alter COLUMN F_KEYID varchar(50) END GO if not exists(select * from T_WORKERQUEUEPARAMETER where F_ID = 'EmptyRoomWhenCheckout') BEGIN INSERT INTO T_WORKERQUEUEPARAMETER (F_ID, F_VALUE, F_SHOPID, F_REMARK, F_ISHIDE, F_TYPE,F_VERSION,F_ADDDATE) VALUES ('EmptyRoomWhenCheckout', '0', NULL, '新结账时置空房间', 0, '系统','18.1.126','2018-07-03'); END GO if not exists(select * from T_WORKERQUEUEPARAMETER where F_ID = 'CheckWorkerIsDestineWhenInputWorker') BEGIN INSERT INTO T_WORKERQUEUEPARAMETER (F_ID, F_VALUE, F_SHOPID, F_REMARK, F_ISHIDE, F_TYPE,F_VERSION,F_ADDDATE) VALUES ('CheckWorkerIsDestineWhenInputWorker', '1', NULL, '新结账时录单时检查员工是否被预约', 0, '系统','18.1.126','2018-07-04'); END GO if not exists(select * from T_WORKERQUEUEPARAMETER where F_ID = 'RemindWhenStopDispatch') BEGIN INSERT INTO T_WORKERQUEUEPARAMETER (F_ID, F_VALUE, F_SHOPID, F_REMARK, F_ISHIDE, F_TYPE) VALUES ('RemindWhenStopDispatch', '房间%s需要手动排钟', NULL, '暂停排钟后有排钟语音', 0, '小索辅助'); END GO if not exists(select * from T_WORKERQUEUEPARAMETER where F_ID = 'RoomExpensesRoomTypeIDs') BEGIN INSERT INTO T_WORKERQUEUEPARAMETER (F_ID, F_VALUE, F_SHOPID, F_REMARK, F_ISHIDE, F_TYPE,F_VERSION, F_ADDDATE) VALUES ('RoomExpensesRoomTypeIDs', '', NULL, '包厢费的包厢类型(房间类型编号之间用,分隔)', 0, '基础设置', '18.1.126', '2018-07-09'); END GO if not exists(select * from T_WORKERQUEUEPARAMETER where F_ID = 'RoomExpensesGoodsID') BEGIN INSERT INTO T_WORKERQUEUEPARAMETER (F_ID, F_VALUE, F_SHOPID, F_REMARK, F_ISHIDE, F_TYPE,F_VERSION, F_ADDDATE) VALUES ('RoomExpensesGoodsID', '', NULL, '包厢费的项目编号 留空:不开启 填写:开启', 0, '基础设置', '18.1.126', '2018-07-09'); END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_ROOM') and Name='F_LASTDIRTYTIME') BEGIN ALTER TABLE T_ROOM ADD F_LASTDIRTYTIME DATETIME NULL END GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GETROOMSTATEANDCOSTTIME]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[GETROOMSTATEANDCOSTTIME] 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, F_ISDIRTY INTEGER, F_CHECKOUTDATETIME DATETIME, F_LASTDIRTYTIME 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; DECLARE @F_TEMPISDIRTY INTEGER; DECLARE @F_TEMPCHECKOUTDATETIME DATETIME; DECLARE @F_TEMPLASTDIRTYTIME DATETIME; 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, F_ISDIRTY, F_CHECKOUTDATETIME, F_LASTDIRTYTIME 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, @F_TEMPISDIRTY, @F_TEMPCHECKOUTDATETIME,@F_TEMPLASTDIRTYTIME; 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; IF (@F_TEMPISDIRTY = 1) BEGIN SET @F_TEMPSTATE = '已结账'; 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, @F_TEMPISDIRTY, @F_TEMPCHECKOUTDATETIME,@F_TEMPLASTDIRTYTIME ); 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, @F_TEMPISDIRTY, @F_TEMPCHECKOUTDATETIME,@F_TEMPLASTDIRTYTIME; END; CLOSE @ROOMCURSOR; DEALLOCATE @ROOMCURSOR; RETURN; END; GO if not exists(select * from T_WORKERQUEUEPARAMETER where F_ID = 'BathRoomCanChangeRoomStart') BEGIN INSERT INTO T_WORKERQUEUEPARAMETER (F_ID, F_VALUE, F_SHOPID, F_REMARK, F_ISHIDE, F_TYPE,F_VERSION, F_ADDDATE) VALUES ('BathRoomCanChangeRoomStart', '1', NULL, '浴场房间是否可以转房起钟', 0, '浴场', '18.1.126', '2018-07-16'); 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