ALTER 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 ELSE IF (@F_TEMPSTATE = '空闲') BEGIN SET @ReceiveCount = 0; SELECT @ReceiveCount = COUNT(1) FROM T_RECEIVE WHERE F_SHOPID = @shopid AND F_ROOMID = @F_TEMPID AND F_CHECKOUTID IS NULL; IF(@ReceiveCount > 0) BEGIN SET @F_TEMPSTATE = '占用'; END; END IF (@F_TEMPISDIRTY = 1) BEGIN SET @F_TEMPSTATE = '已结账'; END; SELECT @F_TEMPCHECKINAMOUNT = COALESCE(SUM(T_CONSUME.F_AMOUNT*T_GOODS.F_HUMANAMOUNT), 0) FROM T_CONSUME INNER JOIN T_RECEIVE ON T_RECEIVE.F_ID = T_CONSUME.F_RECEIVEID INNER JOIN T_GOODS ON T_GOODS.F_ID = T_CONSUME.F_GOODSID WHERE T_RECEIVE.F_SHOPID = @shopid AND T_RECEIVE.F_ROOMID = @F_TEMPID AND T_RECEIVE.F_CHECKOUTID IS NULL; --查询出来的数据插入返回表中 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;