SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- =============================================
-- 查询会员信息、折扣、兑换券
-- 返回两个记录集--1.会员基本信息、折扣;2.兑换券信息
--
-- =============================================
-- =============================================
-- Update date: 2014-12-17
-- Publisher: hanxiaodong
-- Ticket: #10994
-- =============================================
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_gateway_inquery_interface_pos]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_gateway_inquery_interface_pos]
GO
---查询会员信息
CREATE PROCEDURE [dbo].[sp_gateway_inquery_interface_pos]
@xmlinput TEXT, ---XML消息
@hotel_code varchar(20), ---酒店代码
@cipher_password as varchar(100), ---密文密码
@clear_password as varchar(100) ---明文密码
AS
begin
SET NOCOUNT ON
---定义变量
declare @hdoc INT
DECLARE @sp_version INT
DECLARE @gh_no VARCHAR(50)
declare @card_no varchar(20)
declare @id_no varchar(20)
declare @mobile_no varchar(20)
declare @place_pos varchar(20)
declare @place varchar(20)
declare @source_pos varchar(20)
declare @source varchar(20)
declare @guests int
declare @status varchar(1)
declare @expiry_date datetime
declare @food_discount decimal(18,2) ---食品折扣
declare @beve_discount decimal(18,2) ---酒水折扣
declare @misc_discount decimal(18,2) ---其他折扣
declare @discount_1 decimal(18,2) ---多业态折扣，从1-10
declare @discount_2 decimal(18,2)
declare @discount_3 decimal(18,2)
declare @discount_4 decimal(18,2)
declare @discount_5 decimal(18,2)
declare @discount_6 decimal(18,2)
declare @discount_7 decimal(18,2)
declare @discount_8 decimal(18,2)
declare @discount_9 decimal(18,2)
declare @discount_10 decimal(18,2)
declare @account_balance decimal(18,2)
declare @local_balance decimal(18,2)
declare @overdraft decimal(18,2)
declare @points int
declare @chname varchar(50)
declare @engname varchar(100)
declare @membership_type_code varchar(20)
declare @membership_type_name varchar(20)
declare @vip_level_code varchar(20)
declare @vip_level_name varchar(1000)
declare @english_name varchar(100)
declare @card_sn varchar(20)
declare @birthday datetime
declare @true_password varchar(100)
declare @sqlstr nvarchar(4000)
declare @data_type varchar(10) --查询过滤的条件:CARDNO/IDNO/MOBILE
declare @data_value varchar(20)
declare @history_type varchar(20) ---消费类型，餐饮为F，多业态为自定义类型
declare @business_date datetime
declare @final_card_no varchar(20)
declare @memo varchar(200)
---返回结果
declare @result_flag as varchar(1)
declare @result_message as varchar(1000)
---系统参数
declare @truncated_card_prefix_len int ---匹配卡号时,需要去掉磁条中卡号前缀的长度
declare @add_card_prefix varchar(100) ---匹配卡号时,自动增加的卡号前缀
declare @check_card_expiry_date varchar(1) ---查询会员、结帐时是否要检验卡有效期
declare @check_pwd_inquery varchar(1) ---查询会员信息时，是否校验密码
declare @check_pwd_cardtype varchar(1000) ---哪些卡类型需要校验密码
declare @check_password_encryption_method as varchar(10) --接收的密码是明文还是密文
declare @display_member_coupons as varchar(10) ---查询结果是否包含会员拥有的券
declare @inquery_card_cardtype varchar(20) ---哪个卡类型可以通过手机号码，证件号码来查询会员
---默认值
SET @sp_version=1
set @result_flag = 'A'
set @result_message=''
set @food_discount=0 ---食品折扣
set @beve_discount=0 ---酒水折扣
set @misc_discount=0 ---其他折扣
set @final_card_no=''
---系统参数赋值
set @truncated_card_prefix_len = cast(isnull(dbo.fn_get_sysparam_value('POS_TRUNCATED_CARD_PREFIX_LEN',''), 0) as int)
set @add_card_prefix = isnull(dbo.fn_get_sysparam_value('POS_ADD_CARD_PREFIX',''), '')
set @check_card_expiry_date = isnull(dbo.fn_get_sysparam_value('POS_CHECK_CARD_EXPIRY_DATE',@hotel_code), 'N')
set @check_pwd_inquery = isnull(dbo.fn_get_sysparam_value('POS_CHECK_PWD_INQUERY',@hotel_code), 'N')
set @check_pwd_cardtype = isnull(dbo.fn_get_sysparam_value('POS_CHECK_PWD_CARDTYPE',@hotel_code), '')
set @check_password_encryption_method = dbo.fn_get_sysparam_value('POS_CHECK_CARD_PASSWORD_ENCRYPTION_METHOD',@hotel_code)
set @display_member_coupons = dbo.fn_get_sysparam_value('POS_DISPLAY_MEMBER_COUPONS',@hotel_code)
set @inquery_card_cardtype = dbo.fn_get_sysparam_value('POS_INQUERY_CARD_CARDTYPE','')
EXEC sp_xml_preparedocument @hdoc output,@xmlinput
---从XML取得基本信息
SELECT @data_value=data_value,
@business_date=business_date,
@place_pos=place,
@source_pos=source,
@guests=PosPlatform
FROM OPENXML (@hdoc,'SVCMessage',1)
WITH
(
data_value VARCHAR(50) 'SVAN',
business_date datetime 'BusinessDate',
place VARCHAR(10) 'RevenueCenter',
source VARCHAR(10) 'TerminalType',
PosPlatform int 'PosPlatform'
)
/*获得消费类型和LPS交易场所*/
select @history_type=dhtp.history_type_code,@place=dp.code
from dic_place dp
join dic_history_type_place dhtp on dp.code=dhtp.place_code
and dp.pos_code=@place_pos
join dic_place_m dpm on dp.m_code=dpm.code and dpm.hotel_code=@hotel_code
if ISNULL(@history_type,'')=''
begin
set @result_flag = 'D'
set @result_message = 'Wrong outlet and consumption type'
GOTO LAST_END
end
if ISNULL(@place,'')=''
begin
set @result_flag = 'D'
set @result_message = 'Wrong outlet.'
GOTO LAST_END
end
/*获得消费来源order type*/
select @source=code from dic_source_svc
where pos_id=@source_pos
and hotel_code=@hotel_code
if ISNULL(@source,'') =''
begin
set @result_flag = 'D'
set @result_message = 'Wrong order type.'
GOTO LAST_END
end
/*内部逻辑,查询条件中如果第一位是M，表示用手机号查询；如果第一位是N，表示身份证查询。否则都视为卡号*/
if LEFT(@data_value,1)='M' and LEN(@data_value)=12 and SUBSTRING(@data_value,2,1) = '1'
begin
set @data_type='MOBILE'
set @mobile_no=RIGHT(@data_value,LEN(@data_value)-1)
end
else if LEFT(@data_value,1)='N'
begin
set @data_type='IDNO'
set @id_no=RIGHT(@data_value,LEN(@data_value)-1)
end
else
begin
set @data_type='CARDNO'
set @card_no=@data_value
end
---处理卡号 begin
if @data_type='CARDNO'
begin
set @card_no = substring(@card_no,@truncated_card_prefix_len+1,len(@card_no)-@truncated_card_prefix_len)
if isnull(@add_card_prefix,'')<>''
begin
set @card_no = @add_card_prefix + @card_no
end
end
---处理卡号 end
---检查会员是否存在
if @data_type='CARDNO'
begin
select @final_card_no= card_no from member_info_svc where card_no=@card_no
end
else if @data_type='MOBILE'
begin
select @final_card_no= card_no from member_info_svc where linkman_tel=@mobile_no and membership_type=@inquery_card_cardtype
end
else if @data_type='IDNO'
begin
select @final_card_no= card_no from member_info_svc where id_no=@id_no and membership_type=@inquery_card_cardtype
end
if isnull(@final_card_no,'')=''
begin
set @result_flag = 'D'
if @data_type='CARDNO'
begin
set @result_message = 'Wrong Card No.'
end
else if @data_type='MOBILE'
begin
set @result_message = 'Wrong Mobile.'
end
else if @data_type='IDNO'
begin
set @result_message = 'Wrong ID No.'
end
GOTO LAST_END
end
else
begin
---获得会员信息
---有附属卡功能
if exists(
select gh_no from member_info_svc
where isnull(main_gh_no,'')<>'' and card_no=@final_card_no
)
begin
select top 1 @gh_no=a.gh_no,@status=d.state,@expiry_date=d.expiry_date,
@account_balance=a.balance,@local_balance=a.balance-a.deposit,
@overdraft=isnull(c.overdraft_spend,0),@points=a.points-a.deposit_points,
@chname=d.chname,@engname=d.engname,@birthday=d.birthday,
@membership_type_code=b.code,@membership_type_name=b.name,
@vip_level_code=e.code,@vip_level_name=e.name,
@english_name=d.engname,@card_sn=d.card_sn,
@memo=a.memo
from member_info_svc a,dic_card_membership b,member_info_sub c,
member_info_svc d,dic_vip_level_svc e
where a.membership_type=b.code and d.gh_no = c.parent_gh_no
and a.gh_no = d.main_gh_no and c.main_flag='1'
and d.vip_level=e.code
and b.code=e.membership_code
and d.card_no=@final_card_no
end
---没有附属卡功能
else
begin
---主卡信息
select top 1 @gh_no=a.gh_no,@status=a.state,@expiry_date=a.expiry_date,
@account_balance=a.balance,@local_balance=a.balance-a.deposit,
@overdraft=isnull(c.overdraft_spend,0),@points=a.points-a.deposit_points,
@chname=a.chname,@engname=a.engname,@birthday=a.birthday,
@membership_type_code=b.code,@membership_type_name=b.name,
@vip_level_code=e.code,@vip_level_name=e.name,
@english_name=a.engname,@card_sn=a.card_sn,
@memo=a.memo
from member_info_svc a,dic_card_membership b,member_info_sub c,
dic_vip_level_svc e
where a.membership_type=b.code and a.gh_no = c.parent_gh_no
and c.main_flag='1'
and a.vip_level=e.code and b.code=e.membership_code
and a.card_no=@final_card_no
end
end
---校验会员卡状态
---判断卡状态若卡状态为消卡的情况下则操作不成功
if @result_flag='A' and @status = 'C'
begin
set @result_flag = 'D'
set @result_message = 'The card is canceled.'
GOTO LAST_END
end
---判断卡状态若卡状态为删除的情况下则操作不成功
if @result_flag='A' and @status = 'D'
begin
set @result_flag = 'D'
set @result_message = 'The card is deleted.'
GOTO LAST_END
end
---判断卡状态若卡状态为冻结的情况下则操作不成功
if @result_flag='A' and @status = 'P'
begin
set @result_flag = 'D'
set @result_message = 'The card is frozen.'
GOTO LAST_END
end
---检查会员卡是否到期
if @result_flag='A' and @check_card_expiry_date = 'Y' and datediff(d,@expiry_date,getdate())>0
begin
set @result_flag = 'D'
set @result_message = 'Card has expired.'
GOTO LAST_END
end
---校验会员卡密码
if @check_password_encryption_method='CIPHER'
begin
set @true_password=@cipher_password
end
else
begin
set @true_password=@clear_password
end
if @result_flag='A' and @check_pwd_inquery='Y' and @check_pwd_cardtype<>''
begin
if not exists (
select gh_no from member_info_svc
where
(card_password=@true_password
or membership_type not in (select splitValue from dbo.fn_split(@check_pwd_cardtype,','))
)
and card_no=@final_card_no
)
begin
set @result_flag = 'D'
set @result_message = 'Wrong card password.'
GOTO LAST_END
end
end
---
if @result_flag='A'
begin
---获得折扣，放在临时表中 begin
create table #temp_discount (
to_dt datetime,
disc_food decimal(18,2),
disc_beve decimal(18,2),
disc_misc decimal(18,2),
disc_1 decimal(18,2),
disc_2 decimal(18,2),
disc_3 decimal(18,2),
disc_4 decimal(18,2),
disc_5 decimal(18,2),
disc_6 decimal(18,2),
disc_7 decimal(18,2),
disc_8 decimal(18,2),
disc_9 decimal(18,2),
disc_10 decimal(18,2)
)
if @history_type='F'
begin
insert into #temp_discount (to_dt,disc_food,disc_beve,disc_misc)
exec sp_get_discount_by_rule_ifc_new '12',@final_card_no,@hotel_code,@place_pos,@source_pos,@guests
end
else
begin
insert into #temp_discount (to_dt,disc_food,disc_beve,disc_misc,disc_1,disc_2,disc_3,disc_4,disc_5,disc_6,disc_7,disc_8,disc_9,disc_10)
exec sp_get_discount_by_rule_ifc_new '13',@final_card_no,@hotel_code,@place_pos,@source_pos,@guests
end
select @food_discount=disc_food,@beve_discount=disc_beve,@misc_discount=disc_misc
,@discount_1=isnull(disc_1,0),@discount_2=isnull(disc_2,0),@discount_3=isnull(disc_3,0)
,@discount_4=isnull(disc_4,0),@discount_5=isnull(disc_5,0),@discount_6=isnull(disc_6,0)
,@discount_7=isnull(disc_7,0),@discount_8=isnull(disc_8,0),@discount_9=isnull(disc_9,0)
,@discount_10=isnull(disc_10,0)
from #temp_discount
drop table #temp_discount
end
LAST_END:
select @result_flag AS ResponseCode,
@result_message AS DisplayMessage,
@chname AS AccountName,
@membership_type_code+'['+@membership_type_code+']' AS Card_Type,
@local_balance AS LocalBalance,
@account_balance AS AccountBalance,
@birthday AS Birthday,
@points AS PointsBalance,
@food_discount AS FoodDiscount,
@beve_discount AS BeveDiscount,
@misc_discount AS MiscDiscount,
@discount_1 AS Discount_1,
@discount_2 AS Discount_2,
@discount_3 AS Discount_3,
@discount_4 AS Discount_4,
@discount_5 AS Discount_5,
@discount_6 AS Discount_6,
@discount_7 AS Discount_7,
@discount_8 AS Discount_8,
@discount_9 AS Discount_9,
@discount_10 AS Discount_10,
@expiry_date AS ExpiryDate,
@vip_level_code as CardLevelCode,
@vip_level_name as CardLevelName,
@english_name as EnglishName,
@card_sn as CardSN,
@final_card_no as SVAN,
@memo as Remark,
b.coupon_type,
b.coupon_name,
ISNULL(b.coupon_number,0) as coupon_number,
@sp_version AS sp_version
from
(
select 1 as code1
) a left join
(
---获得会员已有的券 begin
select c.code as coupon_type,c.name as coupon_name,
sum(a.exchange) as coupon_number
from points_exchange_svc a,dic_points_coupons c
where a.gh_no=@gh_no
AND a.coupons_type=c.code
and a.coupon_status<>'USE'
and @display_member_coupons='Y'
AND @business_date BETWEEN a.use_begin AND a.use_end
AND a.coupon_status='SEND'
AND a.accrue_type<>'ROLL_OUT'
group by c.code,c.name
---获得会员已有的券 end
) b
on 1=1
SET NOCOUNT OFF
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
2019.11.8