--@18.0.122.1524 --修改会员手机权限 IF NOT EXISTS (SELECT * FROM T_POPEDOM WHERE F_ID = 3342) INSERT T_POPEDOM (F_ID,F_NAME,F_VALUE) VALUES(3342,'修改会员手机',300) ; GO --修改提成系数 IF NOT EXISTS (SELECT * FROM T_POPEDOM WHERE F_ID = 8354) INSERT T_POPEDOM (F_ID,F_NAME,F_VALUE) VALUES(8354,'修改提成系数',301) ; GO --玉河湾版本合并 if not exists(select * from T_CHECKOUTTYPE where F_ID = 'W23') BEGIN insert into T_CHECKOUTTYPE VALUES('W23', '消费赠送', null, 0 ,1, 0 ,0 ); END GO IF OBJECT_ID (N'T_SCHEME_DATETIME') IS NULL BEGIN --如果不存在该表，则进行创建 CREATE TABLE [dbo].[T_SCHEME_DATETIME]( [F_ID] [varchar](20) NOT NULL, [F_NAME] [varchar](100) NULL, [F_SCHEMEID] [varchar](20) NOT NULL, [F_STARTDATETIME] [datetime] NULL, [F_ENDDATETIME] [datetime] NULL, [F_WEEK] [int] NULL, [F_SHOPID] [varchar](10) NULL, CONSTRAINT [PK_T_SCHEME_DATETIME] 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_SCHEME_DETAILS') IS NULL BEGIN --如果不存在该表，则进行创建 CREATE TABLE [dbo].[T_SCHEME_DETAILS]( [F_ID] [varchar](10) NOT NULL, [F_SCHEMEID] [varchar](20) NOT NULL, [F_GOODSID] [varchar](20) NOT NULL, [F_SHOPID] [varchar](20) NULL, CONSTRAINT [PK_T_SCHEME_DETAILS] 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_GOODS_SCHEME') IS NULL BEGIN --如果不存在该表，则进行创建 CREATE TABLE [dbo].[T_GOODS_SCHEME]( [F_ID] [varchar](10) NOT NULL, [F_NAME] [varchar](50) NOT NULL, [F_REMARK] [varchar](50) NULL, [F_SHOPID] [varchar](10) NOT NULL, [F_DATETIME] [datetime] NOT NULL, [F_VALUE] [decimal](9, 2) NOT NULL, [F_PRIORITY] [int] NULL, CONSTRAINT [PK_T_GOODS_SCHEME] 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 EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'V_GOODS_SCHEME') BEGIN DROP View V_GOODS_SCHEME; END GO CREATE VIEW [dbo].[V_GOODS_SCHEME] AS SELECT dbo.T_GOODS_SCHEME.F_ID, dbo.T_GOODS_SCHEME.F_NAME, dbo.T_GOODS_SCHEME.F_REMARK, dbo.T_GOODS_SCHEME.F_SHOPID, dbo.T_GOODS_SCHEME.F_DATETIME, dbo.T_GOODS_SCHEME.F_VALUE, dbo.T_GOODS_SCHEME.F_PRIORITY, dbo.T_SCHEME_DETAILS.F_GOODSID, dbo.V_GOODS.F_NAME AS F_GOODSNAME, dbo.V_GOODS.F_UNITNAME, dbo.V_GOODS.F_PRICE, dbo.V_GOODS.F_CANREBATE, dbo.V_GOODS.F_GOODSTYPENAME, dbo.V_GOODS.F_COST, dbo.V_GOODS.F_GOODSTYPE1NAME, dbo.V_GOODS.F_STATE, dbo.V_GOODS.F_ISSALE, dbo.V_GOODS.F_ISSTORAGE, dbo.V_GOODS.F_SHOPID AS F_GOODSSHOPID, dbo.V_GOODS.F_SHOWID, dbo.V_GOODS.F_AVAILABLE, dbo.V_GOODS.F_HUMANAMOUNT, dbo.V_GOODS.F_ISSMALL FROM dbo.T_GOODS_SCHEME INNER JOIN dbo.T_SCHEME_DETAILS ON dbo.T_GOODS_SCHEME.F_ID = dbo.T_SCHEME_DETAILS.F_SCHEMEID INNER JOIN dbo.V_GOODS ON dbo.T_SCHEME_DETAILS.F_GOODSID = dbo.V_GOODS.F_ID GO IF EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_CHECKOUT') and Name='F_SUPPLEMENTAUTOID') BEGIN ALTER TABLE T_CHECKOUT alter COLUMN F_SUPPLEMENTAUTOID varchar(20) END GO IF EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_CHECKOUT_H') and Name='F_SUPPLEMENTAUTOID') BEGIN ALTER TABLE T_CHECKOUT_H alter COLUMN F_SUPPLEMENTAUTOID varchar(20) END GO if not exists(select * from T_WORKERQUEUEPARAMETER where F_ID = 'EnableConsumptiveGift') BEGIN INSERT INTO T_WORKERQUEUEPARAMETER (F_ID, F_VALUE, F_SHOPID, F_REMARK, F_ISHIDE, F_TYPE) VALUES ('EnableConsumptiveGift', '', NULL, '是否启用返优惠活动', 0, '营销活动'); END GO if not exists(select * from T_WORKERQUEUEPARAMETER where F_ID = 'ConsumptiveGiftStartDateTime') BEGIN INSERT INTO T_WORKERQUEUEPARAMETER (F_ID, F_VALUE, F_SHOPID, F_REMARK, F_ISHIDE, F_TYPE) VALUES ('ConsumptiveGiftStartDateTime', '', NULL, '返优惠活动开始时候', 0, '营销活动'); END GO if not exists(select * from T_WORKERQUEUEPARAMETER where F_ID = 'ConsumptiveGiftEndtDateTime') BEGIN INSERT INTO T_WORKERQUEUEPARAMETER (F_ID, F_VALUE, F_SHOPID, F_REMARK, F_ISHIDE, F_TYPE) VALUES ('ConsumptiveGiftEndtDateTime', '', NULL, '返优惠活动结束时间', 0, '营销活动'); END GO if not exists(select * from T_WORKERQUEUEPARAMETER where F_ID = 'ConsumptiveGiftTimeNode') BEGIN INSERT INTO T_WORKERQUEUEPARAMETER (F_ID, F_VALUE, F_SHOPID, F_REMARK, F_ISHIDE, F_TYPE) VALUES ('ConsumptiveGiftTimeNode', '', NULL, '返优惠时间点', 0, '营销活动'); END GO IF EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_SUPPLEMENT') and Name='F_REMARK') BEGIN ALTER TABLE T_SUPPLEMENT alter COLUMN F_REMARK varchar(2500) END GO IF EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_SUPPLEMENT_H') and Name='F_REMARK') BEGIN ALTER TABLE T_SUPPLEMENT_H alter COLUMN F_REMARK varchar(2500) END GO IF OBJECT_ID (N'T_SUPPLEMENT_SCHEME') IS NULL BEGIN --如果不存在该表，则进行创建 CREATE TABLE [dbo].[T_SUPPLEMENT_SCHEME]( [F_ID] [varchar](20) NOT NULL, [F_SUPPLEMENTID] [varchar](20) NOT NULL, [F_CHECKOUTID] [varchar](20) NOT NULL, [F_SHOPID] [varchar](20) NULL, CONSTRAINT [PK_T_SUPPLEMENT_SCHEME] 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_CARDTYPE') and Name='F_ISCOPYMONEYWITHSCALE') BEGIN ALTER TABLE T_CARDTYPE ADD F_ISCOPYMONEYWITHSCALE smallint NULL DEFAULT 0 END GO --充值金额限制为100的倍数 IF NOT EXISTS( SELECT * FROM syscolumns WHERE id=object_id('T_CARDTYPE') and Name='F_ISHUNDRED') BEGIN ALTER TABLE T_CARDTYPE ADD F_ISHUNDRED smallint NULL DEFAULT 0 END GO --挂单管理 IF OBJECT_ID (N'T_HANGRECEIVE') IS NULL BEGIN --如果不存在该表，则进行创建 CREATE TABLE [dbo].[T_HANGRECEIVE]( [F_ID] [varchar](20) NOT NULL, [F_REASON] [varchar](200) NULL, [F_RECEIVEID] [varchar](20) NULL, [F_USERID] [varchar](20) NULL, [F_DATETIME] [datetime] NULL, [F_REMARK] [varchar](500) NULL, [F_ISCANCELED] [smallint] NULL, [F_CANCELID] [varchar](20) NULL, [F_ROOMID] [varchar](20) NULL, [F_SHOPID] [varchar](10) NULL, [F_WORKER] [varchar](20) NULL, CONSTRAINT [PK_T_HANGRECEIVE] 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 T_POPEDOM WHERE F_ID = 3361) INSERT T_POPEDOM (F_ID,F_NAME,F_VALUE) VALUES(3361,'修改会员卡类型',302) ; 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 = 'IsBathRoomNeedHandBrandOnlyOne') BEGIN INSERT INTO T_WORKERQUEUEPARAMETER (F_ID, F_VALUE, F_SHOPID, F_REMARK, F_ISHIDE, F_TYPE,F_VERSION,F_ADDDATE) VALUES ('IsBathRoomNeedHandBrandOnlyOne', '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 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 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