昆仑会员注册机

[landv]

基本licens注册

select hotel_code,hotel_ch_name,register_code,expire_date,module,function_control from license 

DECLARE @hotel_code varchar(20),            -- 酒店code
@hotel_ch_name varchar(500),        -- 酒店中文名
@module varchar(1000),                -- 注册模块
@register_code varchar(500),        -- 注册码
@expire_date varchar(50),            -- 过期时间
@function_control varchar(2000)=''    ---功能控制，都控制哪些功能，用逗号分隔

set @hotel_code='1234' --酒店Code
set @hotel_ch_name ='酒店' --酒店中文名
set @module ='CCM|2019-07-31,CSM|2019-07-31,LPS|2019-07-31' --注册模块
set @expire_date='2019-07-31'--过期时间
set @function_control='BAR:N;PROMOTION:N;POINTSRESV:N;FFP:N;'---功能控制，都控制哪些功能，用逗号分隔
declare @license varchar(500)
set @license=dbo.fn_int_to_hex(ABS(CHECKSUM(@hotel_code+@expire_date)))        
        +'-'+dbo.fn_int_to_hex(ABS(CHECKSUM(@hotel_code+@module)))               
        +'-'+dbo.fn_int_to_hex(ABS(CHECKSUM('石基昆仑'+@hotel_code)))      
        +'-'+dbo.fn_int_to_hex(ABS(CHECKSUM(@hotel_ch_name+@function_control)))
select @license

--dbo.fn_int_to_hex

自动注册

select hotel_code,hotel_ch_name,register_code,expire_date,module,function_control from license 

declare @id int;
set @id =(select sum(1) from license);
select @id --输出id

select * into License_bak from License instal 
select * from License_bak 
    update License_bak set register_code='ss'

--自动注册开始    
DECLARE My_Cursor CURSOR --定义游标
FOR (select hotel_code,hotel_ch_name from license ) --查出需要的集合放到游标中
OPEN My_Cursor; --打开游标
declare @hotel_code varchar(20)    --声明一个变量，用于读取游标中的值
declare @hotel_ch_name varchar(500)    --声明一个变量，用于读取游标中的值
--fetch next from My_Cursor 
--select @noToUpdate
FETCH NEXT FROM My_Cursor into @hotel_code,@hotel_ch_name ; --读取第一行数据
WHILE @@FETCH_STATUS = 0
BEGIN

    DECLARE
    @module varchar(1000),                -- 注册模块
    @register_code varchar(500),        -- 注册码
    @expire_date varchar(50),            -- 过期时间
    @function_control varchar(2000)=''    ---功能控制，都控制哪些功能，用逗号分隔

    set @module ='CCM|2021-07-31,CSM|2021-07-31,LPS|2021-07-31' --注册模块
    set @expire_date='2021-07-31'--过期时间
    set @function_control='BAR:N;PROMOTION:N;POINTSRESV:N;FFP:N;'---功能控制，都控制哪些功能，用逗号分隔
    declare @license varchar(500)
    set @license=dbo.fn_int_to_hex(ABS(CHECKSUM(@hotel_code+@expire_date)))        
            +'-'+dbo.fn_int_to_hex(ABS(CHECKSUM(@hotel_code+@module)))               
            +'-'+dbo.fn_int_to_hex(ABS(CHECKSUM('石基昆仑'+@hotel_code)))      
            +'-'+dbo.fn_int_to_hex(ABS(CHECKSUM(@hotel_ch_name+@function_control)))
--select @license
    update license set register_code=@license , expire_date=@expire_date,module=@module,function_control=@function_control WHERE hotel_ch_name =@hotel_ch_name
 FETCH NEXT FROM My_Cursor into @hotel_code,@hotel_ch_name; --读取下一行数据
 END
CLOSE My_Cursor; --关闭游标
DEALLOCATE My_Cursor; --释放游标
--自动注册结束

dbo.fn_int_to_hex

USE ECRS
GO
SET ANSI_NULLS, QUOTED_IDENTIFIER OFF
GO

CREATE FUNCTION [dbo].[fn_int_to_hex]
(
@original_int int
)
returns  varchar(15)

AS
begin
  declare @hex_str varchar(15)
  set @hex_str=''

  while @original_int/16>0
  begin
      set @hex_str=
      (case 
      when (@original_int % 16)<=9 then convert(varchar(1),@original_int % 16)
      when (@original_int % 16)=10 then 'A'
      when (@original_int % 16)=11 then 'B'
      when (@original_int % 16)=12 then 'C'
      when (@original_int % 16)=13 then 'D'
      when (@original_int % 16)=14 then 'E'
      when (@original_int % 16)=15 then 'F'
      end)
      +@hex_str
      set @original_int=@original_int/16

  end
  if @original_int>0 
    set @hex_str=(case 
                  when (@original_int % 16)<=9 then convert(varchar(1),@original_int % 16)
                  when (@original_int % 16)=10 then 'A'
                  when (@original_int % 16)=11 then 'B'
                  when (@original_int % 16)=12 then 'C'
                  when (@original_int % 16)=13 then 'D'
                  when (@original_int % 16)=14 then 'E'
                  when (@original_int % 16)=15 then 'F'
                  end)+@hex_str
  return @hex_str

end

GO

数据库需要更新位置：select hotel_code,hotel_ch_name,register_code,expire_date,module,function_control from license 通过软件界面需要更新license地址 　　xxxx:5959/ecrs_train/sysmanage/sysinfo/registerList.asp

kws扩展license注册

DECLARE @chain_name varchar(100),            -- 集团名称
@channel_code varchar(20),        -- 渠道代码
@channel_name varchar(100),        -- 渠道名称
@module varchar(1000),                -- 注册模块
@expire_date varchar(50),            -- 过期时间
@function_control varchar(200)=''    ---功能控制，都控制哪些功能，用逗号分隔

set @chain_name ='华中集团'        -- 集团名称
set @channel_code = 'WEC'      -- 渠道代码
set @channel_name = '微信|WEIXIN'        -- 渠道名称
set @module ='Availability,Reservation,Profile,Information,Security,Comment,Payment,Wechat'                -- 注册模块
set @expire_date ='2020-07-31'              -- 过期时间

declare @license varchar(500)
set @license=dbo.fn_int_to_hex(ABS(CHECKSUM(@channel_code+@expire_date+@chain_name)))        
        +'-'+dbo.fn_int_to_hex(ABS(CHECKSUM(@channel_code+@module+@chain_name)))        
        +'-'+dbo.fn_int_to_hex(ABS(CHECKSUM('石基昆仑'+@channel_code+@chain_name)))      
        +'-'+dbo.fn_int_to_hex(ABS(CHECKSUM(@channel_name+@chain_name+@function_control)))
select @license

存储过程相关

sp_check_license

USE ECRS
GO
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO

CREATE proc sp_check_license
@hotel_code varchar(20),            -- 酒店code
@hotel_ch_name varchar(500),        -- 酒店中文名
@module varchar(1000),                -- 注册模块
@register_code varchar(500),        -- 注册码
@expire_date varchar(50),            -- 过期时间
@update_user varchar(50),            -- 过期时间
@register_id varchar(20),            -- 修改本表的id
@function_control varchar(2000)=''    ---功能控制，都控制哪些功能，用逗号分隔
with encryption
as
begin
set nocount on    
    declare @license varchar(500)
    set @license=dbo.fn_int_to_hex(ABS(CHECKSUM(@hotel_code+@expire_date)))        
        +'-'+dbo.fn_int_to_hex(ABS(CHECKSUM(@hotel_code+@module)))               
        +'-'+dbo.fn_int_to_hex(ABS(CHECKSUM('石基昆仑'+@hotel_code)))      
        +'-'+dbo.fn_int_to_hex(ABS(CHECKSUM(@hotel_ch_name+@function_control)))

    if @license<>@register_code
    begin
        select -1 as register_id
    end 
    else
    begin
        if (isnull(@register_id,'') = '')
        begin
            if not exists(select * from license where hotel_code=@hotel_code)
            begin

                insert license(hotel_code,hotel_ch_name,module,register_code,register_date,expire_date,insert_user,insert_date,update_user,update_date,function_control)
                values(@hotel_code,@hotel_ch_name,@module,@register_code,getdate(),@expire_date,@update_user,getdate(),@update_user,getdate(),@function_control)

                update dic_systems set 
                    expire_dt=right(splitValue,len(splitValue)-4)
                    ,update_date=getdate()
                from  (select splitValue from dbo.fn_split(@module,','))a
                where a.splitValue like dic_systems.code+'%'
                and dic_systems.expire_dt<right(a.splitValue,len(a.splitValue)-4)

                select SCOPE_IDENTITY() as register_id

            end
            else
            begin
                select -2 as register_id
            end
        end
        else
        begin
            if  exists(select * from license where hotel_code=@hotel_code)
            begin

                update license set hotel_code = @hotel_code,hotel_ch_name = @hotel_ch_name,module = @module,
                    register_code = @register_code,register_date = getdate(),
                    expire_date = @expire_date,update_user=@update_user,update_date=getdate(),
                    function_control=@function_control
                where id = @register_id

                update dic_systems set 
                    expire_dt=right(splitValue,len(splitValue)-4)
                    ,update_date=getdate()
                from  (select splitValue from dbo.fn_split(@module,','))a
                where a.splitValue like dic_systems.code+'%'
                and dic_systems.expire_dt<right(a.splitValue,len(a.splitValue)-4)

                select @register_id as register_id

            end
            else
            begin
                select -3 as register_id
            end
        end 
    end
set nocount off
end

GO

sp_check_license_kws

USE ECRS
GO
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO

CREATE proc sp_check_license_kws
@channel_code varchar(20)            -- 酒店code
with encryption
as
begin
set nocount on    

    declare @channel_name varchar(500)        -- 酒店中文名
    declare @module varchar(1000)            -- 注册模块
    declare @register_code varchar(500)        -- 注册码
    declare @expire_date varchar(50)            -- 过期时间
    declare @chain_name varchar(100)        ---集团名称
    declare @function_control varchar(200)  ---功能控制，都控制哪些功能，用逗号分隔

    select @channel_name=b.name
         ,@module=a."service"
         ,@register_code=a.register_code
         ,@expire_date=convert(varchar(10),a.expire_date,120)
        ,@chain_name= 
        (
            select top 1 name from hotels where flag='1'
        ) 
        ,@function_control = a.function_control
    from license_kws a ,dic_channel b
    where b.code=a.channel_code and b.code=@channel_code

    declare @license varchar(500)
    set @license=dbo.fn_int_to_hex(ABS(CHECKSUM(@channel_code+@expire_date+@chain_name)))        
        +'-'+dbo.fn_int_to_hex(ABS(CHECKSUM(@channel_code+@module+@chain_name)))        
        +'-'+dbo.fn_int_to_hex(ABS(CHECKSUM('石基昆仑'+@channel_code+@chain_name)))      
        +'-'+dbo.fn_int_to_hex(ABS(CHECKSUM(@channel_name+@chain_name+@function_control)))

    if @license=@register_code
    begin
        select @chain_name as chain_name
            ,@channel_code as channel_code
            ,@channel_name as channel_name
            ,@module as "service"
            ,@expire_date as expire_date    
            ,@function_control as function_control
    end
    else
    begin
        select  @chain_name as chain_name
            ,@channel_code as channel_code
            ,@channel_name as channel_name
            ,@module as "service"
            ,@expire_date as expire_date
            ,@function_control as function_control     
        where 1<>1
    end

set nocount off
end

GO

sp_check_license_kws

USE ECRS
GO
SET ANSI_NULLS, QUOTED_IDENTIFIER ON
GO

CREATE proc sp_check_license_kws_save
@chain_name varchar(100),            -- 集团名称
@channel_code varchar(20),        -- 渠道代码
@channel_name varchar(100),        -- 渠道名称
@module varchar(1000),                -- 注册模块
@register_code varchar(500),        -- 注册码
@expire_date varchar(50),            -- 过期时间
@update_user varchar(50),            -- 过期时间
@register_id varchar(20),            -- 修改本表的id
@function_control varchar(200)=''    ---功能控制，都控制哪些功能，用逗号分隔

with encryption
as
begin
set nocount on

    declare @license varchar(500)
    set @license=dbo.fn_int_to_hex(ABS(CHECKSUM(@channel_code+@expire_date+@chain_name)))        
        +'-'+dbo.fn_int_to_hex(ABS(CHECKSUM(@channel_code+@module+@chain_name)))        
        +'-'+dbo.fn_int_to_hex(ABS(CHECKSUM('石基昆仑'+@channel_code+@chain_name)))      
        +'-'+dbo.fn_int_to_hex(ABS(CHECKSUM(@channel_name+@chain_name+@function_control)))

    if @license<>@register_code
    begin
        select -1 as register_id
    end 
    else
    begin
        if (isnull(@register_id,'') = '')
        begin
            if not exists(select * from license_kws where channel_code=@channel_code)
            begin

                insert license_kws (chain_name,channel_code,channel_name,"service",register_code,register_date,expire_date,function_control,insert_user,insert_date,update_user,update_date)
                values(@chain_name,@channel_code,@channel_name,@module,@register_code,getdate(),@expire_date,@function_control,@update_user,getdate(),@update_user,getdate())

                select SCOPE_IDENTITY() as register_id

            end
            else
            begin
                select -2 as register_id
            end
        end
        else
        begin
            if  exists(select * from license_kws where channel_code=@channel_code)
            begin

                update license_kws set chain_name=@chain_name,channel_code = @channel_code,channel_name = @channel_name,"service" = @module,
                    register_code = @register_code,register_date = getdate(),function_control=@function_control,
                    expire_date = @expire_date,update_user=@update_user,update_date=getdate()
                where id = @register_id

                select @register_id as register_id

            end
            else
            begin
                select -3 as register_id
            end
        end
    end

set nocount off
end

GO