732 lines
29 KiB
Transact-SQL
732 lines
29 KiB
Transact-SQL
|
||
|
||
-- 人员表
|
||
if not exists (select 1 from syscolumns where name = 'wxopenid' and id = (select id from sysobjects where name = 'uc_login'))
|
||
ALTER TABLE uc_login add wxopenid varchar(100) null -- 微信小程序唯一身份
|
||
;
|
||
if not exists (select 1 from syscolumns where name = 'wx_service_id' and id = (select id from sysobjects where name = 'uc_login'))
|
||
ALTER TABLE uc_login add wx_service_id varchar(100) null -- 微信服务号唯一身份证
|
||
;
|
||
if not exists (select 1 from syscolumns where name = 'saleid' and id = (select id from sysobjects where name = 'uc_login'))
|
||
ALTER TABLE uc_login add saleid varchar(4) null -- 销售人员编号
|
||
;
|
||
if not exists (select 1 from syscolumns where name = 'appttrans' and id = (select id from sysobjects where name = 'uc_login'))
|
||
ALTER TABLE uc_login add appttrans char(1) null -- OA是否可做为被转审人
|
||
;
|
||
if not exists (select 1 from syscolumns where name = 'apptsend' and id = (select id from sysobjects where name = 'uc_login'))
|
||
ALTER TABLE uc_login add apptsend char(1) null -- OA是否可做为被抄送人
|
||
;
|
||
if not exists (select 1 from syscolumns where name = 'autosendcond' and id = (select id from sysobjects where name = 'uc_login'))
|
||
ALTER TABLE uc_login add autosendcond varchar(500) null -- 自动抄送 以#隔离
|
||
;
|
||
--预订审批类型
|
||
if object_id('appt_type') is null --必须先判断，该SQL补丁是可以反复执行的
|
||
CREATE TABLE appt_type( -- 审批类型
|
||
[Id] [int] IDENTITY(1,1) PRIMARY key , --主键直接写在字段后面就行，减少语法复杂度，提高可读性
|
||
[appt_name] [varchar](30) NULL, -- 审批类型名称
|
||
[remark] [varchar](200) NULL -- 审批类型备注
|
||
)
|
||
;
|
||
if not exists (select 1 from appt_type) --必须先判断，该SQL补丁是可以反复执行的
|
||
INSERT INTO appt_type (appt_name,remark)VALUES('默认流程','')
|
||
;
|
||
|
||
--预订审批流程表
|
||
if object_id('appt_auditor') is null create table appt_auditor( -- 预订审批流配置
|
||
[id] [int] IDENTITY(1,1) PRIMARY key , -- 主键
|
||
[appt_type_id] [int] NULL, -- 审核类型
|
||
[op_group] [varchar](30) NULL, -- 审批组编号(当为空时，都可操作)
|
||
[op_descript] [varchar](50) NULL, -- 审批组名称
|
||
[pid] [int] NULL, -- (全新可改名为oper_seq)审批流顺序[旧层审批主键:0-首层，关系当前表主键]
|
||
|
||
[custom] int default(0) not null -- 是否支持转审：0-不支持,1-支持(当转审之后，不再回到审批流程)
|
||
);
|
||
if not exists (select 1 from sysobjects as t join syscolumns as c on t.id=c.id where t.name='appt_auditor' and c.name='rejectid')
|
||
alter table appt_auditor add [rejectid] int default(1); -- 驳回回到的节点
|
||
|
||
|
||
--预订主单
|
||
if object_id('appt_summary') is null
|
||
CREATE TABLE appt_summary( --预订主单
|
||
[id] int IDENTITY(1,1) PRIMARY KEY --自增主键
|
||
,[guid] nvarchar(50) not null --GUID
|
||
,[userid] nvarchar(20) NULL --所属用户
|
||
,[createtime] datetime default getdate() NOT NULL --创建时间
|
||
,[appt_typeid] int NULL --预订类型[appt_type.id]：0-不需要审批
|
||
|
||
,[auditor_status] int NULL --审批状态:0-保存,1-待审批,2-审批中,3-审批通过(已生成正式预订数据),4-驳回,
|
||
,[auditor_id] int NULL --待审批角色id:0-无
|
||
,[transfer_audit] nvarchar(50) default('')
|
||
,[appt_type] int default 0 not NULL --订单类型预留字段
|
||
,[no] nvarchar(50) NULL --编号
|
||
|
||
,[buyer_name] nvarchar(50) NULL --组织单位
|
||
,[buyer_address] nvarchar(200) NULL --公司地址
|
||
,[buyer_title] nvarchar(100) NULL --会议名称
|
||
,[leader_name] nvarchar(20) NULL --组织负责人
|
||
,[leader_mobile] nvarchar(20) NULL --组织负责人电话
|
||
|
||
,[leader_room] nvarchar(50) NULL --会议组房号
|
||
,[sale_name] nvarchar(20) NULL --营销部跟单人
|
||
,[sale_mobile] nvarchar(20) NULL --营销部跟单电话
|
||
,[sale_director] nvarchar(20) NULL --营销总监(手工写)，
|
||
,[room_remark] nvarchar(200) NULL --客房总备注
|
||
|
||
,[meeting_remark] nvarchar(200) NULL --会议厅总备注
|
||
,[catering_remark] nvarchar(200) NULL --餐饮总备注
|
||
,[spring_remark] nvarchar(200) NULL --温泉总备注
|
||
,[pid] int default(0) not null -- 编辑前的主键,原始数据从appt_log表中读取
|
||
,[grp_no] nvarchar(100) null -- 团队编号
|
||
|
||
,[start_day] datetime null -- 预定信息最小开始日期
|
||
,[end_day] datetime null -- 预定信息最大结果日期
|
||
,[apply_oa] int default(0) -- 申请OA单状态:0-未占用资源,1-占用资源,2-申请OA单
|
||
,[alter_time] datetime not null -- 订单最后修改时间
|
||
)
|
||
;
|
||
|
||
--客房预订明细表
|
||
if object_id('appt_room') is null
|
||
CREATE TABLE appt_room( -- 客房
|
||
[id] int IDENTITY(1,1) PRIMARY KEY --自增主键
|
||
,[guid] nvarchar(50) not null --GUID
|
||
,[appt_id] int NOT NULL --所属预订主单
|
||
,[room_in] datetime NULL --抵达时间
|
||
,[room_out] datetime NULL --离开时间
|
||
|
||
,[room_group] nvarchar(50) NULL --房型(手输或根据类型选择自动填入)，手输不占用资源
|
||
,[room_code] nvarchar(20) NULL--选择房间类型(手输类型时为空,不占用资源)
|
||
,[room_num] int NOT NULL--房间数量
|
||
,[room_price] money default 0 NOT NULL--房间单价
|
||
,[breakfast_num] int NOT NULL--总早餐数:0-无早餐,
|
||
|
||
,[long_distance] int NOT NULL--长途电话:0-关，1-开,
|
||
,[min_shop] int NOT NULL--房吧:0-关,1-开,
|
||
,[remark] nvarchar(200) NULL --客房备注
|
||
)
|
||
;
|
||
|
||
--会议室预订明细表
|
||
if object_id('appt_meeting') is null
|
||
CREATE TABLE appt_meeting(
|
||
[id] int IDENTITY(1,1) PRIMARY KEY --自增主键
|
||
,[guid] nvarchar(50) not null --GUID
|
||
,[appt_id] int NOT NULL--所属预订主单
|
||
,[meeting_time] datetime NULL-- 会议入场日期
|
||
,[groups] nvarchar(50) NOT NULL --场次:1-早,2-中,3-晚，
|
||
,[meeting_room] nvarchar(50) NULL --会议厅名称(手输或根据类型选择自动填入)，手输不占用资源
|
||
|
||
,[meeting_code] nvarchar(20) NULL --会议厅代码(手输类型时为空,不占用资源)
|
||
,[meeting_rostrum] nvarchar(50) NULL --会议室主席台位
|
||
,[meeting_shape] nvarchar(50) NULL --会议室-台型(课桌)
|
||
,[meeting_num] int NULL --人数:0-待定,
|
||
,[meeting_price] money default 0 not NULL --单价
|
||
|
||
,[meeting_title] nvarchar(50) NULL --横幅内容:空-无横幅,
|
||
,[remark] nvarchar(200) NULL --会议厅备注
|
||
)
|
||
;
|
||
|
||
--餐饮预订明细表
|
||
if object_id('appt_catering') is null
|
||
CREATE TABLE appt_catering(
|
||
[id] int IDENTITY(1,1) PRIMARY KEY --自增主键
|
||
,[guid] nvarchar(50) not null --GUID
|
||
,[appt_id] int NOT NULL--所属预订主单
|
||
,[catering_time] datetime NOT NULL --餐饮-日期
|
||
,[groups] nvarchar(50) NOT NULL --餐别:1-早,2-中,3-晚，
|
||
,[catering_room] nvarchar(50) NULL --餐厅名称
|
||
|
||
,[catering_code] nvarchar(50) NULL--用餐地点
|
||
,[catering_type] int default 0 not null -- 用餐类型 0-桌数，1-人数
|
||
,[catering_num] int default 1 not null -- 桌数或人数数量
|
||
,[catering_price] money default 0 not NULL--每桌或每人单价
|
||
,[catering_table] nvarchar(50) NULL--餐饮-桌型
|
||
|
||
,[wine] nvarchar(50) NULL--酒水
|
||
,[remark] nvarchar(200) NULL --备注
|
||
)
|
||
;
|
||
--温泉预订明细表
|
||
if object_id('appt_spring') is null
|
||
CREATE TABLE appt_spring(
|
||
[id] int IDENTITY(1,1) PRIMARY KEY --自增主键
|
||
,[guid] nvarchar(50) not null --GUID
|
||
,[appt_id] int NOT NULL --所属预订主单
|
||
,[spring_time] datetime NULL --温泉_日期
|
||
,[people_num] int NULL--总人数(自动计算):0-待定
|
||
,[spring_men] int NULL--男性人数:0-待定
|
||
|
||
,[spring_women] int NULL--女性人数:0-待定
|
||
,[consume] nvarchar(50) NULL--二次消费
|
||
,[spring_price] money default 0 not NULL--单价
|
||
,[remark] nvarchar(200) NULL --备注
|
||
)
|
||
;
|
||
--预订备注扩展表
|
||
if object_id('appt_remark') is null
|
||
CREATE TABLE appt_remark(
|
||
[id] int IDENTITY(1,1) PRIMARY KEY --自增主键
|
||
,[guid] nvarchar(50) not null --GUID
|
||
,[appt_id] int NOT NULL --预订主键备注
|
||
,[title] nvarchar(50) NULL--备注分类
|
||
,[info] nvarchar(500) NULL --备注内容
|
||
)
|
||
;
|
||
-- 预订审批记录及抄送表
|
||
if object_id('appt_audit_log') is null
|
||
CREATE TABLE appt_audit_log(
|
||
[id] int IDENTITY(1,1) PRIMARY KEY --自增主键
|
||
,[type] int NOT NULL --类型:1-审批,2-抄送，
|
||
,[createtime] datetime default getdate() NOT NULL--创建时间
|
||
,[userid] nvarchar(20) NOT NULL--审批人员/抄送接收人
|
||
,[appt_id] int NOT NULL--所属预订主单
|
||
|
||
,[auditor_status] int NOT NULL--审批状态:1-审批通过,0-驳回,2-转审批,3-手动抄送与自动抄送
|
||
,[remark] nvarchar(200) NULL--审批备注
|
||
,[pic0] nvarchar(max) -- 图片0
|
||
,[pic1] nvarchar(max) -- 图片1
|
||
,[pic2] nvarchar(max) -- 图片2
|
||
|
||
,[pic3] nvarchar(max) -- 图片3
|
||
,[pic4] nvarchar(max) -- 图片4
|
||
,[source] nvarchar(20) NULL --抄送发起人
|
||
,[pid] int not null default 0 -- 被回复的父结点
|
||
)
|
||
;
|
||
--日志表
|
||
if object_id('appt_log') is null
|
||
CREATE TABLE appt_log(
|
||
[id] [int] IDENTITY(1,1) PRIMARY KEY, --自增主键
|
||
[tableName] [varchar](100) NULL,-- 数据表
|
||
[keyId] [varchar](100) NULL, --表主键值
|
||
[oldData] [varchar](2000) NULL, --原始数据
|
||
[newData] [varchar](2000) NULL, --新数据
|
||
[createTime] [datetime] default getdate() NOT NULL, --创建时间
|
||
[userId] [nvarchar](20) NULL, --操作人
|
||
[systemUser] [int] NULL --户类型:0-普通用户,1-管理人员
|
||
)
|
||
;
|
||
--操作展示日志
|
||
if object_id('appt_remark_log') is null
|
||
CREATE TABLE appt_remark_log(
|
||
[id] [int] IDENTITY(1,1) PRIMARY KEY --自增主键
|
||
,[createTime] [datetime] default getdate() NOT NULL --创建时间
|
||
,[pid] [int] default(0) not null -- 所属父级
|
||
,[appt_id] [int] NOT NULL --所属预订主单
|
||
,[account] [nvarchar](20) not null --操作人账号
|
||
|
||
,[username] [nvarchar](20) null --操作人姓名
|
||
,[old_status] [int] not null -- 提交前状态
|
||
,[new_status] [int] not null -- 提交后状态
|
||
,[remark] [varchar](2000) NULL --操作人备注文字
|
||
,[update_data] [varchar](2000) NULL, --数据变化
|
||
)
|
||
;
|
||
|
||
-- 按日期段取每日可售房数
|
||
|
||
if object_id('p_yyj_ota_type_rsv_tend') is not null
|
||
drop proc p_yyj_ota_type_rsv_tend;
|
||
|
||
create proc p_yyj_ota_type_rsv_tend
|
||
@rm_type varchar(255), -- 房型参数，可以多选，用#隔开，如#BJ#DJ# , 也可以用 % 代表全部房型
|
||
@s_time datetime, -- 开始日期
|
||
@e_time datetime, -- 结束日期
|
||
@tentative char(1) = 'T' -- 包含不确定 OTA默认传递 T
|
||
|
||
as
|
||
update room_type set sequence = 10 where sequence = 0
|
||
select @s_time = convert(datetime, convert(char(10), @s_time, 111))
|
||
select @e_time = convert(datetime, convert(char(10), @e_time, 111))
|
||
|
||
declare
|
||
@result int
|
||
-- ------------------------------------------------------------------------------------
|
||
-- 返回值如下
|
||
-- ------------------------------------------------------------------------------------
|
||
create table #inventory --本过程返回值
|
||
(
|
||
date datetime null, --日期
|
||
type char(3) null, --房型
|
||
sequence int null, --排序
|
||
quan int null --可卖数（可用数量）
|
||
)
|
||
create table #tmp
|
||
(
|
||
type char(3) null,
|
||
quan int null
|
||
)
|
||
|
||
declare @type char(3),
|
||
@tennum int
|
||
|
||
-- 拼字符串
|
||
if @rm_type='%'
|
||
begin
|
||
select @rm_type='#'
|
||
select @type=isnull((select min(id) from room_type where id>''), '')
|
||
while @type <> ''
|
||
begin
|
||
select @rm_type = @rm_type+substring(@type+space(3), 1, 3)+'#'
|
||
select @type=isnull((select min(id) from room_type where id>@type ), '')
|
||
end
|
||
end
|
||
else
|
||
select @rm_type = '#'+@rm_type+'#' --强制加上YYJ
|
||
|
||
|
||
while @s_time <= @e_time
|
||
begin
|
||
|
||
|
||
-- 首先插入预留
|
||
if @tentative = 'T'
|
||
begin
|
||
|
||
insert #inventory select @s_time, c.id, 0,
|
||
quan=isnull((select sum(a.rooms) from rsvinfo a, mstinfo b
|
||
where charindex('#'+substring(a.type+' ',1,3)+'#',@rm_type)>0 and a.begin_<>a.end_ and a.begin_<=@s_time and a.end_>@s_time and substring(a.accnt,1,1) <> 'G%'
|
||
and a.accnt=b.accnt and (a.roomno is null or a.roomno='') and c.id = b.type
|
||
and b.sta in ('R','I')
|
||
),0) + (select isnull(count(distinct a.roomno),0) from rsvinfo a, mstinfo b
|
||
where charindex('#'+substring(a.type+' ',1,3)+'#',@rm_type)>0 and a.begin_<>a.end_ and a.begin_<=@s_time and a.end_>@s_time and substring(a.accnt,1,1) <> 'G%'
|
||
and a.accnt=b.accnt and a.roomno is not null and a.roomno<>'' and c.id = b.type
|
||
and b.sta in ('R','I'))
|
||
from room_type c where charindex('#'+substring(c.id+' ',1,3)+'#', @rm_type) > 0
|
||
|
||
end
|
||
else
|
||
begin
|
||
delete #tmp
|
||
insert #inventory select @s_time, c.id, 0,
|
||
quan=isnull((select sum(a.rooms) from rsvinfo a, mstinfo b
|
||
where charindex('#'+substring(a.type+' ',1,3)+'#',@rm_type)>0 and a.begin_<>a.end_ and a.begin_<=@s_time and a.end_>@s_time and substring(a.accnt,1,1) <> 'G%'
|
||
and a.accnt=b.accnt and (a.roomno is null or a.roomno='') and c.id = b.type
|
||
and b.sta in ('R','I') and b.resclass not in (select id from sys_code where class='resclass' and charindex('Tentative',remark)>0)
|
||
),0) + (select isnull(count(distinct a.roomno),0) from rsvinfo a, mstinfo b
|
||
where charindex('#'+substring(a.type+' ',1,3)+'#',@rm_type)>0 and a.begin_<>a.end_ and a.begin_<=@s_time and a.end_>@s_time and substring(a.accnt,1,1) <> 'G%'
|
||
and a.accnt=b.accnt and a.roomno is not null and a.roomno<>'' and c.id = b.type
|
||
and b.sta in ('R','I') and b.resclass not in (select id from sys_code where class='resclass' and charindex('Tentative',remark)>0))
|
||
from room_type c where charindex('#'+substring(c.id+' ',1,3)+'#', @rm_type) > 0
|
||
|
||
end
|
||
|
||
-- modi by shr 2014.4.3
|
||
update #inventory set #inventory.quan =(select isnull(count(c.roomno),0) from room_list c where #inventory.type=C.type and
|
||
c.tag1 <> 'VR') - #inventory.quan where #inventory.date = @s_time and #inventory.type<>'TEN'
|
||
|
||
update #inventory set #inventory.quan = #inventory.quan - (select isnull(count(1),0) from room_oo b where b.sta='I' and b.type='OO'
|
||
and b.bdate <= @s_time and b.edate > @s_time and b.rmtype = #inventory.type)
|
||
where #inventory.date = @s_time and #inventory.type<>'TEN'
|
||
|
||
-- YYJ 可用与占用增加超预留累加
|
||
if exists(select 1 from sys_option where catalog = 'reserver' and item = 'rsvtendovr' and rtrim(value)='T')
|
||
update #inventory set #inventory.quan = #inventory.quan + (select isnull(sum(exceed),0) from room_type b where b.id = #inventory.type)
|
||
where #inventory.date = @s_time and #inventory.type<>'TEN'
|
||
|
||
|
||
|
||
select @s_time = dateadd(day, 1, @s_time)
|
||
end
|
||
|
||
|
||
update #inventory set sequence = a.sequence from room_type a where rtrim(#inventory.type)=rtrim(a.id)
|
||
select a.date,a.type,a.sequence,a.quan
|
||
from #inventory a order by a.date, a.sequence
|
||
|
||
return 0
|
||
;
|
||
if object_id('p_yyj_grpadd_inter') is not null
|
||
drop proc p_yyj_grpadd_inter ;
|
||
|
||
create proc p_yyj_grpadd_inter
|
||
@gname varchar(50) , --团队名称
|
||
@leader char(20), --领队或导游或联系人
|
||
@phone char(20), --领队电话
|
||
@arr datetime , --抵达时间 日期和时间，如2019-01-17 18:00:00
|
||
@dep datetime, --离开时间 日期和时间，如2019-01-18 14:00:00
|
||
@rooms integer, --团队总共的房间数备注（只是备注，不占用房间）
|
||
@guests integer, --团队的总人数备注（只是备注，不占用房间）
|
||
@remark varchar(255), --团队主单备注
|
||
@log_id char(5), --操作员工号（如果操作员没有软件的工号，第三方可以在迪联软件中创建一个通用的工号
|
||
@log_ip char(16), --操作电脑或者接口电脑的IP，可以用空字符串或者任意字符串
|
||
@saleid char(4) --销售员工号，在迪联软件销售菜单下可以打开销售员设置，查看销售员的工号，可以为空字符换
|
||
as
|
||
|
||
declare
|
||
@resno char(10) ,
|
||
@accnt varchar(10),
|
||
@msg varchar(60),
|
||
@ret integer ,
|
||
@sysdate datetime,
|
||
@newno char(10)
|
||
|
||
select @ret = 0
|
||
select @msg = ''
|
||
|
||
if rtrim(isnull(@gname,'')) = ''
|
||
select @msg = '团队名称不允许空'
|
||
if @arr = null or @dep= null
|
||
select @msg = '抵离时间不允许为空'
|
||
if @rooms = null or @rooms=0
|
||
select @rooms =1
|
||
if @guests = null or @guests=0
|
||
select @guests =1
|
||
if datediff(dd,@arr,@dep)<1
|
||
select @msg = '离开日期必须大于入住日期'
|
||
|
||
if @msg <> ''
|
||
begin
|
||
select @ret = 1
|
||
select @ret , @msg
|
||
return @ret
|
||
end
|
||
|
||
select @sysdate = sysdate from sys_data
|
||
select @resno = no from sys_acctno where class ='reserve'
|
||
select @accnt = no from sys_acctno where class ='group'
|
||
|
||
if exists (select 1 from grpinfo where accnt =@accnt)
|
||
begin
|
||
select @accnt = "G0" + right("00000" + convert(varchar(4),convert(integer,right(max(rtrim(accnt)),4))+1),5) from grpinfo
|
||
end
|
||
|
||
insert grpinfo( accnt,sta, gname,src,mkt,arr,dep,country, leader,phone,guests,rooms,cropsno,cropname,paycode,
|
||
ramark,sysdate,log_num,log_id,log_shift,log_ip,log_date,navite,address,payroom,ename,resno,
|
||
grpno,payaccnt,code,vip,sec,special,araccnt,saleid)
|
||
select @accnt,'R',@gname,'LOC','GRP',@arr,@dep,'CN',@leader,@phone,@guests,@rooms,'','','9001',
|
||
@remark, @sysdate,0,@log_id,'1',@log_ip,getdate(),'','','团队','',@resno,
|
||
@accnt,@accnt,'G','0','0','','',@saleid
|
||
|
||
if exists (select 1 from grpinfo where accnt =@accnt)
|
||
begin
|
||
select @newno = "G0" + right("00000" + convert(varchar(4),convert(integer,(right(rtrim(@accnt),4))) + 1),5)
|
||
update sys_acctno set no = @newno where class ='group'
|
||
select @newno = "R0" + right("00000" + convert(varchar(4),convert(integer,(right(rtrim(@resno),4))) + 1),5)
|
||
update sys_acctno set no =@newno where class ='reserve'
|
||
end
|
||
select @ret = 0
|
||
select @msg = @accnt
|
||
select @ret,@msg
|
||
return @ret
|
||
;
|
||
|
||
if object_id('p_yyj_grpmeet_inter') is not null
|
||
drop proc p_yyj_grpmeet_inter;
|
||
|
||
create proc p_yyj_grpmeet_inter
|
||
@accnt char(7), --团队账号（创建团队时获得）
|
||
@date datetime, --会议预订的日期 只要日期不需要时分秒
|
||
@rescode varchar(3), --会议室编号,可以从迪联软件查看，或者res_code表取对照code字段
|
||
@sfield integer, --场次， 0代表全天 1上午场 2下午场 3晚场
|
||
@price money, --价格。可以从迪联软件查看，或者res_code表取price字段，该字段为预设参考价，具体订单价格以该参数为准。
|
||
@op_id char(5) --操作员工号（如果操作员没有软件的工号，第三方可以在迪联软件中创建一个通用的工号
|
||
as
|
||
|
||
|
||
declare
|
||
@sysno char(6) ,
|
||
@maxno integer ,
|
||
@no char(10),
|
||
@bdate datetime ,
|
||
@op_name varchar(16),
|
||
@sfieldtime varchar(5),
|
||
@efieldtime varchar(5)
|
||
|
||
if not exists( select 1 from grpinfo where sta in ('I','R') and accnt = @accnt )
|
||
begin
|
||
select 1,'团队账号 ' + @accnt + '非有效状态'
|
||
return 1
|
||
end
|
||
if exists (select 1 from res_info where @date>=stime and @date<=etime and rescode = @rescode and (sfield = @sfield or sfield=0 or @sfield=0) and sta in ('R','O'))
|
||
begin
|
||
select 1,'该会议室已被预订!'
|
||
return 1
|
||
end
|
||
select @op_name = op_name from uc_login where op_id = @op_id
|
||
select @bdate = sysdate from sys_data
|
||
select @sysno = convert ( char ( 6 ) , getdate ( ) , 12 ) from sys_data
|
||
select @maxno = isnull ( max ( convert ( int , substring ( no , 7 , 4 ) ) ) , 0 ) + 1 from res_info where
|
||
substring ( no , 1 , 6 ) =convert ( char ( 6 ) , getdate ( ) , 12 )
|
||
select @no = @sysno + right('0000' + convert(varchar(4),@maxno),4)
|
||
if @sfield = 0
|
||
begin
|
||
select @sfieldtime = '08:30'
|
||
select @efieldtime = '22:30'
|
||
end
|
||
else if @sfield = 1
|
||
begin
|
||
select @sfieldtime = '08:30'
|
||
select @efieldtime = '11:30'
|
||
end
|
||
else if @sfield = 2
|
||
begin
|
||
select @sfieldtime = '13:00'
|
||
select @efieldtime = '17:30'
|
||
end
|
||
else if @sfield = 3
|
||
begin
|
||
select @sfieldtime = '19:30'
|
||
select @efieldtime = '22:30'
|
||
end
|
||
|
||
|
||
|
||
INSERT INTO res_info select
|
||
@no,'R',@accnt,'',@rescode,1,@bdate,@date,@date,@sfield,@sfield,
|
||
@sfieldtime,@efieldtime,NULL,@price,@price,'F',@op_id,@op_name,
|
||
getdate(),NULL,NULL,NULL,0
|
||
if @@rowcount = 1
|
||
select 0,'会议室预定成功!'
|
||
else
|
||
select 1,'会议室预定失败，请重试!'
|
||
return 0
|
||
;
|
||
|
||
if object_id('p_yyj_gblock_inter') is not null
|
||
drop proc p_yyj_gblock_inter ;
|
||
create proc p_yyj_gblock_inter
|
||
@log_id char(5), --操作员工号（如果操作员没有软件的工号，第三方可以在迪联软件中创建一个通用的工号）
|
||
@log_ip char(16), --操作电脑或者接口电脑的IP，可以用空字符串或者任意字符串
|
||
@grpno char(7), --团队账号（创建团队时获得）
|
||
@arr datetime, --抵达日期 只要日期不需要时分秒
|
||
@dep datetime, --离开日期 只要日期不需要时分秒
|
||
@type char(3), --房类代码，如BJ，请查看迪联酒店系统中房类的设置
|
||
@rooms integer, --预留的数量，此为真实数量，如果超过酒店可用数会返回错误信息
|
||
@price money, --房价
|
||
@guests integer, -- 一个房间住几个人，默认为1
|
||
@remark varchar(255), --该预留的备注，排房时可以看到此备注
|
||
@rmcode char(4) --请按照默认的RACK填写，此为房价码
|
||
|
||
as
|
||
declare
|
||
@ret integer,
|
||
@accnt char(10),
|
||
@resno char(10),
|
||
@sysdate datetime,
|
||
@price0 money,
|
||
@log_name char(12),
|
||
@mkt char(3),
|
||
@src char(3),
|
||
@gname varchar(50),
|
||
@ename char(50),
|
||
@country char(3),
|
||
@vip char(2),
|
||
@sec char(2),
|
||
@cropsno char(7),
|
||
@special varchar(50),
|
||
@saleid char(4),
|
||
@araccnt char(10),
|
||
@msg varchar(60),
|
||
@deptime varchar(8),
|
||
@postopen char(1),
|
||
@rmpackage varchar(50),
|
||
@ooo integer,
|
||
@blk integer,
|
||
@avl integer
|
||
|
||
select @rmpackage = ''
|
||
select @postopen = isnull(value,'') from sys_option where catalog = 'group' and item = 'postopen'
|
||
if @postopen is null
|
||
set @postopen = 'F'
|
||
if charindex(@postopen,'TF') = 0
|
||
set @postopen = 'F'
|
||
|
||
if not exists( select 1 from grpinfo where sta in ('I','R') and accnt = @grpno )
|
||
begin
|
||
select @ret=1, @msg = '团队账号 ' + @grpno + '非有效状态'
|
||
select @ret, @msg
|
||
return @ret
|
||
end
|
||
select @ooo = 0
|
||
select @blk=0
|
||
select @avl=0
|
||
exec p_xx_reserve_chkavail @type,@arr,@dep,'N',@ooo out ,@blk out ,@avl out,''
|
||
if @avl is null or @avl<@rooms
|
||
begin
|
||
select @ret=@avl, @msg = '该房类资源不足'
|
||
select @ret, @msg
|
||
return @ret
|
||
end
|
||
|
||
select @sysdate = sysdate from sys_data
|
||
select @log_name = op_name from uc_login where op_id=@log_id
|
||
select @src=src,@mkt=mkt,@gname=gname,@ename=ename,@country=country,@resno=rtrim(resno),@vip=vip,@sec=sec,
|
||
@cropsno=cropsno,@special=special,@saleid=saleid,@araccnt=araccnt from grpinfo where accnt = @grpno
|
||
if @resno is null
|
||
exec p_xx_pub_getno 'RESE',@resno output
|
||
|
||
select @deptime = deptime from mkt_deptime where id = 'GRP' and isnull(flag,'') = 'T'
|
||
if @deptime <> '' and @deptime is not null
|
||
begin
|
||
select @dep = convert(datetime,(rtrim(left(convert(varchar,@dep,120),10)) + ' ' + @deptime))
|
||
end
|
||
|
||
select @price0 = price0 from room_type where id=@type
|
||
exec p_xx_pub_getno 'ACCT',@accnt output
|
||
|
||
insert mstinfo(accnt,grpno,sta,src,mkt,sysdate,resno,arr,dep,type,roomno,price0,price,rooms,numbs,gname,ename,sex,
|
||
vip,sec,cropsno,special,saleid,araccnt,country,rmcode,remark,log_id,log_name,log_shift,log_ip,log_date,logmark,res,restime,idcls,rmpackage,flag)
|
||
select @accnt,@grpno,'R',@src,@mkt,@sysdate,@resno,@arr,@dep,@type,'',@price0,@price,@rooms,@guests,@gname,@ename,'M',
|
||
@vip,@sec,@cropsno,@special,@saleid,@araccnt,@country,@rmcode,@remark,@log_id,@log_name,'1',@log_ip,getdate(),0,@log_id,getdate(),'01',@rmpackage,@postopen
|
||
|
||
|
||
select @ret = 0, @msg = '房类预留成功'
|
||
select @ret, @msg
|
||
return @ret
|
||
;
|
||
|
||
if object_id('p_xx_res_info_view3') is not null
|
||
drop proc p_xx_res_info_view3 ;
|
||
create proc p_xx_res_info_view3
|
||
@startday datetime,
|
||
@enddate datetime,
|
||
@sort varchar(3),
|
||
@accnt varchar(10) = '%'
|
||
as
|
||
|
||
declare
|
||
@rdate datetime,
|
||
@accnt1 varchar(10)
|
||
if exists(select 1 from mstinfo where accnt=@accnt)
|
||
select @accnt1=grpno from mstinfo where accnt=@accnt
|
||
select @rdate=@startday
|
||
|
||
CREATE TABLE #query (
|
||
code varchar(6) not null,
|
||
descript char(20) null,
|
||
sort char(3) null,
|
||
day11 char(2) null,
|
||
day12 char(2) null,
|
||
day13 char(2) null,
|
||
day21 char(2) null,
|
||
day22 char(2) null,
|
||
day23 char(2) null,
|
||
day31 char(2) null,
|
||
day32 char(2) null,
|
||
day33 char(2) null,
|
||
day41 char(2) null,
|
||
day42 char(2) null,
|
||
day43 char(2) null,
|
||
day51 char(2) null,
|
||
day52 char(2) null,
|
||
day53 char(2) null,
|
||
day61 char(2) null,
|
||
day62 char(2) null,
|
||
day63 char(2) null,
|
||
day71 char(2) null,
|
||
day72 char(2) null,
|
||
day73 char(2) null,
|
||
day81 char(2) null,
|
||
day82 char(2) null,
|
||
day83 char(2) null,
|
||
day91 char(2) null,
|
||
day92 char(2) null,
|
||
day93 char(2) null
|
||
)
|
||
|
||
insert #query (code,descript,sort)
|
||
select code,descript1,sort from res_code where sta='T' and sort like @sort
|
||
|
||
CREATE TABLE #tblav (
|
||
rescode char(4) not null,
|
||
bdate datetime not null,
|
||
sfield int null,
|
||
accnt varchar(10) null,
|
||
accnt1 varchar(10) null,
|
||
sta char(1) not null)
|
||
insert #tblav(rescode,bdate,sfield,accnt,accnt1,sta)
|
||
select rescode,stime,sfield,accnt,accnt1,min(sta) from res_info where sta in ('R','O')
|
||
group by rescode,stime,sfield,accnt,accnt1
|
||
order by rescode,stime,sfield,accnt,accnt1
|
||
|
||
update #tblav set sta='S' where accnt=@accnt or accnt1=@accnt1
|
||
|
||
select @rdate=dateadd(day,0,@startday)
|
||
|
||
update #query set #query.day11=b.sta + '0' from #tblav b where #query.code=b.rescode and datediff(dd,b.bdate,@rdate)=0 and b.sfield=0
|
||
|
||
update #query set #query.day11=b.sta from #tblav b where #query.code=b.rescode and datediff(dd,b.bdate,@rdate)=0 and b.sfield=1
|
||
update #query set #query.day12=b.sta from #tblav b where #query.code=b.rescode and datediff(dd,b.bdate,@rdate)=0 and b.sfield=2
|
||
update #query set #query.day13=b.sta from #tblav b where #query.code=b.rescode and datediff(dd,b.bdate,@rdate)=0 and b.sfield=3
|
||
|
||
select @rdate=dateadd(day,1,@startday)
|
||
update #query set #query.day21=b.sta + '0' from #tblav b where #query.code=b.rescode and datediff(dd,b.bdate,@rdate)=0 and b.sfield=0
|
||
|
||
update #query set #query.day21=b.sta from #tblav b where #query.code=b.rescode and datediff(dd,b.bdate,@rdate)=0 and b.sfield=1
|
||
update #query set #query.day22=b.sta from #tblav b where #query.code=b.rescode and datediff(dd,b.bdate,@rdate)=0 and b.sfield=2
|
||
update #query set #query.day23=b.sta from #tblav b where #query.code=b.rescode and datediff(dd,b.bdate,@rdate)=0 and b.sfield=3
|
||
|
||
select @rdate=dateadd(day,2,@startday)
|
||
update #query set #query.day31=b.sta + '0' from #tblav b where #query.code=b.rescode and datediff(dd,b.bdate,@rdate)=0 and b.sfield=0
|
||
update #query set #query.day31=b.sta from #tblav b where #query.code=b.rescode and datediff(dd,b.bdate,@rdate)=0 and b.sfield=1
|
||
update #query set #query.day32=b.sta from #tblav b where #query.code=b.rescode and datediff(dd,b.bdate,@rdate)=0 and b.sfield=2
|
||
update #query set #query.day33=b.sta from #tblav b where #query.code=b.rescode and datediff(dd,b.bdate,@rdate)=0 and b.sfield=3
|
||
|
||
select @rdate=dateadd(day,3,@startday)
|
||
update #query set #query.day41=b.sta + '0' from #tblav b where #query.code=b.rescode and datediff(dd,b.bdate,@rdate)=0 and b.sfield=0
|
||
update #query set #query.day41=b.sta from #tblav b where #query.code=b.rescode and datediff(dd,b.bdate,@rdate)=0 and b.sfield=1
|
||
update #query set #query.day42=b.sta from #tblav b where #query.code=b.rescode and datediff(dd,b.bdate,@rdate)=0 and b.sfield=2
|
||
update #query set #query.day43=b.sta from #tblav b where #query.code=b.rescode and datediff(dd,b.bdate,@rdate)=0 and b.sfield=3
|
||
|
||
select @rdate=dateadd(day,4,@startday)
|
||
update #query set #query.day51=b.sta + '0' from #tblav b where #query.code=b.rescode and datediff(dd,b.bdate,@rdate)=0 and b.sfield=0
|
||
update #query set #query.day51=b.sta from #tblav b where #query.code=b.rescode and datediff(dd,b.bdate,@rdate)=0 and b.sfield=1
|
||
update #query set #query.day52=b.sta from #tblav b where #query.code=b.rescode and datediff(dd,b.bdate,@rdate)=0 and b.sfield=2
|
||
update #query set #query.day53=b.sta from #tblav b where #query.code=b.rescode and datediff(dd,b.bdate,@rdate)=0 and b.sfield=3
|
||
|
||
select @rdate=dateadd(day,5,@startday)
|
||
update #query set #query.day61=b.sta + '0' from #tblav b where #query.code=b.rescode and datediff(dd,b.bdate,@rdate)=0 and b.sfield=0
|
||
update #query set #query.day61=b.sta from #tblav b where #query.code=b.rescode and datediff(dd,b.bdate,@rdate)=0 and b.sfield=1
|
||
update #query set #query.day62=b.sta from #tblav b where #query.code=b.rescode and datediff(dd,b.bdate,@rdate)=0 and b.sfield=2
|
||
update #query set #query.day63=b.sta from #tblav b where #query.code=b.rescode and datediff(dd,b.bdate,@rdate)=0 and b.sfield=3
|
||
|
||
select @rdate=dateadd(day,6,@startday)
|
||
update #query set #query.day71=b.sta + '0' from #tblav b where #query.code=b.rescode and datediff(dd,b.bdate,@rdate)=0 and b.sfield=0
|
||
update #query set #query.day71=b.sta from #tblav b where #query.code=b.rescode and datediff(dd,b.bdate,@rdate)=0 and b.sfield=1
|
||
update #query set #query.day72=b.sta from #tblav b where #query.code=b.rescode and datediff(dd,b.bdate,@rdate)=0 and b.sfield=2
|
||
update #query set #query.day73=b.sta from #tblav b where #query.code=b.rescode and datediff(dd,b.bdate,@rdate)=0 and b.sfield=3
|
||
|
||
select @rdate=dateadd(day,7,@startday)
|
||
update #query set #query.day81=b.sta + '0' from #tblav b where #query.code=b.rescode and datediff(dd,b.bdate,@rdate)=0 and b.sfield=0
|
||
update #query set #query.day81=b.sta from #tblav b where #query.code=b.rescode and datediff(dd,b.bdate,@rdate)=0 and b.sfield=1
|
||
update #query set #query.day82=b.sta from #tblav b where #query.code=b.rescode and datediff(dd,b.bdate,@rdate)=0 and b.sfield=2
|
||
update #query set #query.day83=b.sta from #tblav b where #query.code=b.rescode and datediff(dd,b.bdate,@rdate)=0 and b.sfield=3
|
||
|
||
select @rdate=dateadd(day,8,@startday)
|
||
update #query set #query.day91=b.sta + '0' from #tblav b where #query.code=b.rescode and datediff(dd,b.bdate,@rdate)=0 and b.sfield=0
|
||
update #query set #query.day91=b.sta from #tblav b where #query.code=b.rescode and datediff(dd,b.bdate,@rdate)=0 and b.sfield=1
|
||
update #query set #query.day92=b.sta from #tblav b where #query.code=b.rescode and datediff(dd,b.bdate,@rdate)=0 and b.sfield=2
|
||
update #query set #query.day93=b.sta from #tblav b where #query.code=b.rescode and datediff(dd,b.bdate,@rdate)=0 and b.sfield=3
|
||
|
||
select sort,code,descript,day11,day12,day13,day21,day22,day23,day31,day32,day33,day41,day42,day43,day51,day52,day53,
|
||
day61,day62,day63,day71,day72,day73,day81,day82,day83,day91,day92,day93 from #query order by sort,code
|
||
return 0
|
||
;
|
||
|
||
-- 取消资源占用存储过程
|
||
if object_id('p_yyj_gblock_cancel') is not null
|
||
drop proc p_yyj_gblock_cancel;
|
||
|
||
create proc p_yyj_gblock_cancel
|
||
@accnt char(7), --团队账号（创建团队时获得）
|
||
@op_id char(5) --操作员工号（如果操作员没有软件的工号，第三方可以在迪联软件中创建一个通用的工号
|
||
as
|
||
declare
|
||
@op_name varchar(16),
|
||
@sysdate datetime,
|
||
@ret integer,
|
||
@msg varchar(60)
|
||
|
||
--该过程用于取消该团队的所有预订资源，包括房间、会议室
|
||
|
||
select @sysdate = sysdate from sys_data
|
||
select @op_name = op_name from uc_login where op_id = @op_id
|
||
update res_info set sta='C',cby = @op_id , cbyname = @op_name,cbytime=getdate(),logmark = logmark +1 where accnt = @accnt
|
||
update mstinfo set sta='C',log_id =@op_id , log_shift ='1', log_date =getdate() ,log_name = @op_name,
|
||
logmark = logmark+1 where grpno = @accnt
|
||
select @ret = 0, @msg = '资源取消成功'
|
||
select @ret, @msg
|
||
return @ret
|
||
; |