<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0"
                xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns:msxsl="urn:schemas-microsoft-com:xslt"
                xmlns:user="http://mycompany.com/mynamespace"
                xmlns:js="urn:the-xml-files:xslt-csharp"
                xmlns:ntd='http://www.ntd.ru/ntd/'>
	<xsl:output method="text" />

	<xsl:template match="text()"/>
	<xsl:template match="root/Schemas/Schema">
		<xsl:for-each select ="users/user">
			-- USER
			DECLARE @EntityID INT
			DECLARE @IdentityId uniqueidentifier
			DECLARE @LoginId uniqueidentifier
			DECLARE @userId uniqueidentifier
			DECLARE @type INT
			declare @rc as uniqueidentifier
			DECLARE @contId INT
			declare @DivisionID uniqueidentifier
			declare @salt uniqueidentifier

			if exists(select * from VCLib.T_DIVISIONS)
			set @DivisionID = (select top 1 DivisionID from VCLib.T_DIVISIONS order by TreeLevel)
			else
			begin
			set @DivisionID = newid()
			insert into VCLib.T_DIVISIONS ( DivisionID, Name, FullName )
			values ( @DivisionID, 'Main', 'Main')
			end

			select @type = case when '<xsl:value-of select="../../../Schema/@Name" />' != ''
			then (SELECT EntityID FROM VCLib.T_ENTITIES e WHERE e.[EntName] = 'USERS' and e.[schema] = '<xsl:value-of select="../../../Schema/@Name" />')
			else (SELECT EntityID FROM VCLib.T_ENTITIES e WHERE e.[EntName] = 'USERS')
			end
			
			set @salt = NEWID()

			select top 1 @IdentityId = i.IdentityID, @LoginId = l.LoginID from VCLib.T_IDENTITY i join VCLib.T_LOGIN l on l.[Identity] = i.IdentityID where l.[Login] = '<xsl:value-of select="@Login" />'

			if (@IdentityId is NULL)
			begin

			set @IdentityId = newid()
			set @LoginId = newid()

			insert into VCLib.T_IDENTITY (IdentityID, IdentityName)
			values (@IdentityId, '<xsl:value-of select="@UserName" />')

			insert into VCLib.T_LOGIN (LoginID, [Login], AuthenticationType, Password, Salt, Flags, [Identity], Divisions, IsBlocked)
			values (@LoginId, '<xsl:value-of select="@Login" />', 0, (SELECT HASHBYTES('SHA2_512', '<xsl:value-of select="@Password" />'+cast(@salt as varchar(36))) FOR XML PATH(''), BINARY BASE64), @salt, 0, @IdentityId, @DivisionID, 0)

			end
			else
			begin

			update VCLib.T_IDENTITY
			set IdentityName = '<xsl:value-of select="@UserName" />'
			where IdentityID = @IdentityId

			end

			select top 1 @userId = UserID from VCLib.T_USERS where [Identity] = @IdentityId

			if (@userId is null)
			begin

			set @userId = newid()
			insert into VCLib.T_USERS (UserID, Flags, DivisionID, [Identity], type_)
			values (@userId, 1, @DivisionID, @IdentityId, @type)

			insert into VCCC.T_USERS (UserID)
			values (@userId)

			insert into <xsl:value-of select="../../@Name" />.T_USERS (UserID)
			values (@userId)

			end

			update VCLib.T_users
			set Flags = case when <xsl:value-of select="@Active" /> = 1 then Flags | 1 else Flags &amp; ~1 end
			where userId = @userId

			<xsl:if test="@Remote">
				update VCLib.T_users
				set Flags = case when <xsl:value-of select="@Remote" /> = 1 then Flags | 4 else Flags &amp; ~4 end
				where userId = @userId
			</xsl:if>

			<xsl:for-each select="UserSearches">
				-- user searches
				<xsl:for-each select="UserSearch">
					set @rc = newid()
					SELECT @EntityID = EntityID from VCLib.T_ENTITIES WHERE EntName='<xsl:value-of select="@Entity" />'
					INSERT INTO VCLib.T_USER_SEARCHES(SearchID,EntityID, UserID, SearchName, FilterValue, SimpleFilter)
					VALUES(@rc, @EntityID, @userId, '<xsl:value-of select="@SearchName" />', '<xsl:value-of select="." />', <xsl:value-of select="@SimpleFilter" />)
				</xsl:for-each>

			</xsl:for-each>
			GO
		</xsl:for-each>
	</xsl:template>
</xsl:stylesheet>
