IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_TICKET') and Name='F_BUYERPAYMONEY') BEGIN ALTER TABLE T_TICKET ADD F_BUYERPAYMONEY DECIMAL(9, 2) NULL DEFAULT 0 END GO UPDATE T_TICKET SET F_BUYERPAYMONEY = 0 WHERE F_BUYERPAYMONEY IS NULL; GO IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'V_TICKET') BEGIN DROP View V_TICKET; END GO CREATE VIEW V_TICKET AS SELECT T_TICKET.F_SHOPID , T_TICKET.F_SNID , T_TICKET.F_ID , T_TICKET.F_MONEY , T_TICKET.F_STARTDATE , T_TICKET.F_ENDDATE , T_TICKET.F_STATE , T_TICKET.F_SENDUSER , T_TICKET.F_SENDDATE , T_TICKET.F_SENDSHOPID , T_TICKET.F_CHECKOUTID , T_TICKET.F_CHECKOUTSHOPID , T_TICKET.F_CHECKOUTTIME , T_TICKET.F_CHECKOUTUSER , T_TICKET.F_CLIENTID , T_TICKET.F_PHONE , T_TICKET.F_NAME , T_TICKET.F_REMARK , T_TICKET.F_TYPE , ISNULL(T_TICKETTYPE.F_FACTORTYPE, 0) AS F_FACTORTYPE , T_TICKETTYPE.F_ID AS f_typeid , T_TICKET.F_CONSUMEID , T_TICKET.F_OLDMONEY , T_TICKET.F_RECEIVEID , T_TICKET.F_ClientPromotionID , T_TICKET.F_GivingCount , T_TICKET.F_OpenID , T_TICKET.F_PackageTicketIssuingID, T_TICKET.F_IsECoupon , T_TICKET.F_BUYERPAYMONEY FROM T_TICKET LEFT OUTER JOIN T_TICKETTYPE ON T_TICKETTYPE.F_ID = T_TICKET.F_TYPE; GO IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'V_TICKETFORCHECKOUT') BEGIN DROP View V_TICKETFORCHECKOUT; END GO CREATE VIEW V_TICKETFORCHECKOUT AS SELECT T_TICKET.F_ID , T_TICKET.F_SNID , T_TICKET.F_SENDSHOPID , T_TICKET.F_MONEY , T_TICKET.F_OLDMONEY , T_TICKET.F_CHECKOUTID , T_TICKET.F_TYPE AS T_TYPEID , T_CHECKOUT.F_DATETIME , T_CHECKOUT.F_SHOPID , T_CHECKOUT.F_USERID , T_GOODS.F_ID AS F_GOODSID , T_GOODS.F_NAME AS F_GOODSNAME , T_TICKETTYPE.F_NAME AS T_TYPENAME , T_TICKETTYPE.F_StyleName AS F_TYPESTYLENAME, T_TICKET.F_IsECoupon , T_TICKET.F_BUYERPAYMONEY, (T_TICKETTYPE.F_NAME + CASE WHEN F_IsECoupon = 1 THEN '+' ELSE '' END) AS F_TICKETTYPENAME FROM T_TICKET INNER JOIN T_CHECKOUT ON T_CHECKOUT.F_ID = T_TICKET.F_CHECKOUTID INNER JOIN T_TICKETTYPE ON T_TICKET.F_TYPE = T_TICKETTYPE.F_ID LEFT OUTER JOIN T_CONSUME ON T_CONSUME.F_ID = T_TICKET.F_CONSUMEID LEFT OUTER JOIN T_GOODS ON T_GOODS.F_ID = T_CONSUME.F_GOODSID GO if not exists(select * from T_WORKERQUEUEPARAMETER where F_ID = 'UseClientVCode') BEGIN INSERT INTO T_WORKERQUEUEPARAMETER (F_ID, F_VALUE, F_SHOPID, F_REMARK, F_ISHIDE, F_TYPE,F_VERSION,F_ADDDATE) VALUES ('UseClientVCode', '0', NULL, '是否启用会员验证码', 0, '会员','18.0.123','2018-01-09'); END GO if not exists(select * from T_WORKERQUEUEPARAMETER where F_ID = 'AutoClientVCode') BEGIN INSERT INTO T_WORKERQUEUEPARAMETER (F_ID, F_VALUE, F_SHOPID, F_REMARK, F_ISHIDE, F_TYPE,F_VERSION,F_ADDDATE) VALUES ('AutoClientVCode', '0', NULL, '默认使用验证码并自动发送', 0, '会员','18.0.123','2018-01-09'); END GO if not exists(select * from T_WORKERQUEUEPARAMETER where F_ID = 'PushWeChatWithSoeUserUid') BEGIN INSERT INTO T_WORKERQUEUEPARAMETER (F_ID, F_VALUE, F_SHOPID, F_REMARK, F_ISHIDE, F_TYPE,F_VERSION,F_ADDDATE) VALUES ('PushWeChatWithSoeUserUid', '0', NULL, '采用SoeUserUid推送微信', 0, '微信(新)','18.0.123','2018-01-17'); END GO if not exists(select * from T_WORKERQUEUEPARAMETER where F_ID = 'WeChatAPPID') BEGIN INSERT INTO T_WORKERQUEUEPARAMETER (F_ID, F_VALUE, F_SHOPID, F_REMARK, F_ISHIDE, F_TYPE,F_VERSION,F_ADDDATE) VALUES ('WeChatAPPID', '', NULL, '微信APPID', 0, '微信(新)','18.0.123','2018-01-17'); END GO IF not exists(select * from T_SYSTEM where F_ID = 'CanHasRebateOrVIPPriceWhenNoMoney') BEGIN INSERT INTO dbo.T_SYSTEM ( F_ID , F_VALUE , F_REMARK , F_TYPE , F_VERSION , F_ADDDATE ) VALUES ( 'CanHasRebateOrVIPPriceWhenNoMoney' , -- F_ID - varchar(50) '0' , -- F_VALUE - varchar(3000) '余额为零时是否不享受会员价和折扣' , -- F_REMARK - varchar(200) '会员' , -- F_TYPE - varchar(100) '18.0.123' , -- F_VERSION - varchar(20) '2018-01-17' -- F_ADDDATE - date ) END GO IF NOT EXISTS (SELECT * FROM T_POPEDOM WHERE F_ID = 3361) INSERT T_POPEDOM (F_ID,F_NAME,F_VALUE) VALUES(3361,'修改会员卡类型',302) ; GO if not exists(select * from T_WORKERQUEUEPARAMETER where F_ID = 'IsCanModityCardType') BEGIN INSERT INTO T_WORKERQUEUEPARAMETER (F_ID, F_VALUE, F_SHOPID, F_REMARK, F_ISHIDE, F_TYPE,F_VERSION,F_ADDDATE) VALUES ('IsCanModityCardType', '0', NULL, '是否允许修改会员卡类型', 0, '系统','18.0.123','2018-01-20'); END GO IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_HangReceive') and Name='F_HANDBRANDID') BEGIN ALTER TABLE T_HangReceive ADD F_HANDBRANDID varchar(20) END GO IF EXISTS (select * from sysobjects WHERE NAME='FK_HANGRECEIVE_RECEIVE') ALTER TABLE T_HANGRECEIVE DROP CONSTRAINT FK_HANGRECEIVE_RECEIVE; --如果存在则删除 GO ALTER TABLE T_HANGRECEIVE WITH NOCHECK ADD CONSTRAINT [FK_HANGRECEIVE_RECEIVE] FOREIGN KEY([F_RECEIVEID]) REFERENCES T_RECEIVE ([F_ID]) ON DELETE CASCADE GO IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'V_MINISTERANALYSIS') BEGIN DROP View V_MINISTERANALYSIS; END GO CREATE VIEW 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, T_ROOM.F_ID AS F_ROOMID, T_ROOM.F_SHOWID AS F_ROOMSHOWID, T_HANDBRAND.F_ID AS F_HANDBRANDID, T_HANDBRAND.F_SHOWID AS F_HANDBRANDSHOWID, T_GOODS_1.F_GOODSTYPE1ID AS F_GOODSTYPE1ID, T_GOODS_1.F_STARTTIMES AS F_STARTTIMES 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 LEFT OUTER JOIN dbo.T_ROOM ON dbo.T_RECEIVE.F_ROOMID = 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 = 'IsNotAllowArtChangeNamed') BEGIN INSERT INTO T_WORKERQUEUEPARAMETER (F_ID, F_VALUE, F_SHOPID, F_REMARK, F_ISHIDE, F_TYPE) VALUES ('IsNotAllowArtChangeNamed', '是否不允许技师换点钟', NULL, 'Z8是否不允许技师换点钟', 0, '其他'); END go if not exists(select * from T_WORKERQUEUEPARAMETER where F_ID = 'IsCheckDestineWhenChangeRoom') BEGIN INSERT INTO T_WORKERQUEUEPARAMETER (F_ID, F_VALUE, F_SHOPID, F_REMARK, F_ISHIDE, F_TYPE) VALUES ('IsCheckDestineWhenChangeRoom', '转房后是否检查房间预约', NULL, '转房后是否检查房间预约', 0, '其他'); END go if not exists(select * from T_WORKERQUEUEPARAMETER where F_ID = 'IsArtificerCanCleanRoom') BEGIN INSERT INTO T_WORKERQUEUEPARAMETER (F_ID, F_VALUE, F_SHOPID, F_REMARK, F_ISHIDE, F_TYPE,F_VERSION,F_ADDDATE) VALUES ('IsArtificerCanCleanRoom', '1', NULL, '是否允许技师清洁房间', 0, '其他','18.0.123','2018-01-25'); END GO if not exists(select * from T_WORKERQUEUEPARAMETER where F_ID = 'IsDustManCleanRoomId') BEGIN INSERT INTO T_WORKERQUEUEPARAMETER (F_ID, F_VALUE, F_SHOPID, F_REMARK, F_ISHIDE, F_TYPE,F_VERSION,F_ADDDATE) VALUES ('IsDustManCleanRoomId', '0', NULL, '打扫是否房间置空并包厢内手牌清空到大厅', 0, '浴场','18.0.122','2018-01-27'); END GO if not exists(select * from T_WORKERQUEUEPARAMETER where F_ID = 'IsUseEmployeeWithUser') BEGIN INSERT INTO T_WORKERQUEUEPARAMETER (F_ID, F_VALUE, F_SHOPID, F_REMARK, F_ISHIDE, F_TYPE,F_VERSION,F_ADDDATE) VALUES ('IsUseEmployeeWithUser', '0', NULL, '是否启用职员即操作员功能', 0, '系统','18.0.122','2018-01-27'); END GO IF EXISTS (SELECT * FROM T_POPEDOM WHERE F_ID='3700' ) BEGIN UPDATE dbo.T_POPEDOM SET F_IsTempPopedom=1 WHERE F_ID='3700'; END GO IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'V_WQSTATISTICSNOTFINISHED') BEGIN DROP View V_WQSTATISTICSNOTFINISHED; END GO CREATE VIEW "V_WQSTATISTICSNOTFINISHED"( "F_WORKERID", "F_HEADSHIPID", "F_CONSUMEWORKERID", "F_SHOPID", "F_TYPE", "F_QUEUESTATE", "F_STARTTIMEBYPLAN", "F_STARTTIMEBYGOODS", "F_ENDDATETIME", "F_REMARK") AS select T_WORKERQUEUESTATISTICS.F_WORKERID, T_WORKERQUEUESTATISTICS.F_HEADSHIPID, T_WORKERQUEUESTATISTICS.F_CONSUMEWORKERID, T_RECEIVE.F_SHOPID, T_WORKERQUEUESTATISTICS.F_TYPE, T_WORKERQUEUESTATISTICS.F_QUEUESTATE, T_WORKERQUEUESTATISTICS.F_STARTTIMEBYPLAN, T_WORKERQUEUESTATISTICS.F_STARTTIMEBYGOODS, T_WORKERQUEUESTATISTICS.F_ENDDATETIME, T_WORKERQUEUESTATISTICS.F_REMARK from T_WORKERQUEUESTATISTICS inner join T_CONSUMEWORKER on (T_WORKERQUEUESTATISTICS.F_CONSUMEWORKERID =T_CONSUMEWORKER.F_ID) inner join T_CONSUME on (T_CONSUMEWORKER.F_CONSUMEID = T_CONSUME.F_ID AND T_CONSUME.F_ENDDATETIME is null) inner join T_RECEIVE on (T_CONSUMEWORKER.F_RECEIVEID = T_RECEIVE.F_ID AND T_RECEIVE.F_CHECKOUTID is NULL ) UNION ALL select T_WORKERQUEUESTATISTICS_H.F_WORKERID, T_WORKERQUEUESTATISTICS_H.F_HEADSHIPID, T_WORKERQUEUESTATISTICS_H.F_CONSUMEWORKERID, T_RECEIVE.F_SHOPID, T_WORKERQUEUESTATISTICS_H.F_TYPE, T_WORKERQUEUESTATISTICS_H.F_QUEUESTATE, T_WORKERQUEUESTATISTICS_H.F_STARTTIMEBYPLAN, T_WORKERQUEUESTATISTICS_H.F_STARTTIMEBYGOODS, T_WORKERQUEUESTATISTICS_H.F_ENDDATETIME, T_WORKERQUEUESTATISTICS_H.F_REMARK from T_WORKERQUEUESTATISTICS_H inner join T_CONSUMEWORKER on (T_WORKERQUEUESTATISTICS_H.F_CONSUMEWORKERID =T_CONSUMEWORKER.F_ID) inner join T_CONSUME on (T_CONSUMEWORKER.F_CONSUMEID = T_CONSUME.F_ID AND T_CONSUME.F_ENDDATETIME is null) inner join T_RECEIVE on (T_CONSUMEWORKER.F_RECEIVEID = T_RECEIVE.F_ID and T_RECEIVE.F_CHECKOUTID is null) GO IF EXISTS (select * from dbo.sysobjects where id =object_id(N'GETROOMSTATEANDCOSTTIME') and xtype in (N'FN', N'IF', N'TF')) BEGIN drop function GETROOMSTATEANDCOSTTIME; END GO CREATE FUNCTION [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 FAST_FORWARD 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 if not exists(select * from T_WORKERQUEUEPARAMETER where F_ID = 'EnableOptimizationDraw') BEGIN INSERT INTO T_WORKERQUEUEPARAMETER (F_ID, F_VALUE, F_SHOPID, F_REMARK, F_ISHIDE, F_TYPE,F_VERSION,F_ADDDATE) VALUES ('EnableOptimizationDraw', '0', NULL, '启用优化绘制', 0, '系统','18.0.122','2018-01-31'); END GO IF NOT EXISTS (SELECT * FROM T_POPEDOM WHERE F_ID='1851' ) BEGIN INSERT T_POPEDOM (F_ID,F_NAME,F_VALUE,F_IsTempPopedom) VALUES(1851,'授权结账方式',303,1) ; END GO