/* 
	CUSTOMER FX CORPORATION
	customerfx.com
	
	Developed by Ryan Farley
	11/2/2012
	
	This stored procedure creates a new SalesLogix ID value for
	the table specified in the @Table parameter. Note that this 
	is to be used with caution. This does not use the SalesLogix
	OLE DB Provider and should be avoided in systems with 
	remotes involved.
	
	USAGE: 
	
	To use, delcare an variable to use as the output parameter. 
	This output parameter will contain the new ID value.
	
	-- delcare variable for new ID
	declare @NewKey varchar(12)
	
	-- execute stored procedure
	exec sysdba.GetNewKey @NewKey output, 'ACCOUNT' 
	
	-- or to include a specific or custom sitecode (such as 1234)
	exec sysdba.GetNewKey @NewKey output, 'ACCOUNT', '1234'
	
	-- use new ID value
	select @NewKey
*/

create procedure sysdba.GetNewKey 
( 
	@NewKey varchar(12) output, 
	@Table varchar(32), 
	@SiteCode varchar(4) = 'XXXX') --optional 
as 
	declare @Key varchar(12) 
	declare @Int int 
	declare @Count int 
	declare @Break bit 
	declare @KeyType int 
	declare @KeyDesc char(1)

	select @KeyType = case upper(@Table) 
		when 'ACCOUNT' then 1 
		when 'OPPORTUNITY' then 2 
		when 'CONTACT' then 3 
		when 'ACTIVITY' then 5 
		when 'HISTORY' then 6 
		when 'EVENT' then 7 
		when 'USERNOTIFICATION' then 8 
		when 'ADDRESS' then 22 
		when 'TICKET' then 32 
		when 'ASSOCIATION' then 4 
		when 'PLUGIN' then 29 
		when 'PROCESS' then 9 
		else 25 
	end 
	
	if @KeyType not in (1, 2, 3, 5, 6, 7, 8, 22) 
		set @KeyType = 25 
	
	select @KeyDesc = case @KeyType 
		when 1 then 'A' 
		when 2 then 'O' 
		when 3 then 'C' 
		when 5 then 'V' 
		when 6 then 'H' 
		when 7 then 'E' 
		when 8 then 'n' 
		when 22 then 'a' 
		when 32 then 't' 
		when 4 then 'B' 
		when 29 then 'p' 
		when 9 then 'P' 
		else 'Q' 
	end 

	set transaction isolation level serializable 
	
	begin transaction 
		if @SiteCode = 'XXXX' 
		begin 
			select @SiteCode = (select sitecode from systeminfo 
			where systeminfoid = 'PRIMARY') 
		end 
		
		if not exists(select * from sitekeys where sitecode = @SiteCode and keytype = @KeyType)
		begin
			insert into sitekeys values (@SiteCode, @KeyType, 'A000000', @KeyDesc, getdate(), 'ADMIN', getdate(), 'ADMIN')
		end

		select @Key = KeyValue from sitekeys (updlock) where SiteCode = @SiteCode and KeyType = @KeyType 

		if @Key is null 
		begin 
			rollback 
			return -1001 
		end 

		set @Count = 0 
		set @Break = 0 

		while (@Count < 7) 
		begin 
			set @Int = ascii(substring(@Key, len(@Key) - @Count, 1)) 

			if @Int = 90 
			begin 
				set @Int = 48 
			end 
			else 
			begin 
				if @Int = 57 
				set @Int = 65 
			else 
				set @Int = @Int + 1 
				set @Break = 1 
			end 

		set @Count = @Count + 1 
		set @Key = left(@Key, len(@Key) - @Count) + char(@Int) + 
		right(@Key, @Count - 1) 

		if @Break = 1 
			break 
	end 

	update sitekeys set KeyValue = @Key where sitecode = @SiteCode 
	and keytype = @KeyType 

	if @@rowcount = 0 
	begin 
		rollback 
		return -1002 
	end 

	commit transaction 

	--create new key 
	set @NewKey = @KeyDesc + left(@SiteCode, 4) + @Key 
	return 0