Инструменты пользователя

Инструменты сайта


Боковая панель

Sidebar - вставка данных в блоки

<? $this->SetViewTarget("sidebar_tools_1", 100);?>
<div id="app_sidebar">
Trololo
</div>
<? $this->EndViewTarget();?>
programming:1c-bitrix:ldap-paged-select

LDAP постраничная выборка

declare @dt datetime
declare @id int

set @id=20
set @dt = getdate()


if object_id('tempdb..#users_ad') is not null drop table #users_ad

DECLARE @rc [int] 
DECLARE @query [nvarchar](4000)
DECLARE @samAccountname [nvarchar] (512)

SELECT TOP 900 objectGUID, employeeid, displayName, sn, givenName, company,Title, sAMAccountName, mail, pager, physicalDeliveryOfficeName, extensionattribute2, extensionattribute3, extensionattribute4, extensionattribute5, extensionattribute6, TelephoneNumber, mobile, Manager, ADsPath, facsimileTelephoneNumber, useraccountcontrol
    INTO [#users_ad] 
    FROM OPENQUERY(ADSI, 'SELECT objectGUID, employeeid, displayName, sn, givenName, company,Title, sAMAccountName, mail, pager, physicalDeliveryOfficeName, extensionattribute2, extensionattribute3, extensionattribute4, extensionattribute5, extensionattribute6, TelephoneNumber, mobile, Manager, ADsPath, facsimileTelephoneNumber, useraccountcontrol FROM ''LDAP://DC=MORTON,DC=RU'' WHERE objectCategory=''User'' ORDER BY samAccountname')

set @rc = @@ROWCOUNT

select @samAccountname = MAX(sAMAccountName) 
    FROM #users_ad

select @query = 'SELECT objectGUID, employeeid, displayName, sn, givenName, company,Title, sAMAccountName, mail, pager, physicalDeliveryOfficeName, extensionattribute2, extensionattribute3, extensionattribute4, extensionattribute5, extensionattribute6, TelephoneNumber, mobile, Manager, ADsPath, facsimileTelephoneNumber, useraccountcontrol FROM ''''LDAP://DC=MORTON,DC=RU'''' WHERE objectCategory=''''User'''' and samAccountname > '''''+@samAccountname+''''' ORDER BY samAccountname'
--PRINT @query 
select @query = 'SELECT TOP 900 objectGUID, employeeid, displayName, sn, givenName, company,Title, sAMAccountName, mail, pager, physicalDeliveryOfficeName, extensionattribute2, extensionattribute3, extensionattribute4, extensionattribute5, extensionattribute6, TelephoneNumber, mobile, Manager, ADsPath, facsimileTelephoneNumber, useraccountcontrol FROM OPENQUERY(ADSI, ''' + @query + ''')'
--PRINT @query 

while @rc > 0
begin
    INSERT INTO [#users_ad] ([objectGUID], [employeeid], [displayName], [sn], [givenName], [company], [Title], [sAMAccountName], [mail], [pager], [physicalDeliveryOfficeName], [extensionattribute2], [extensionattribute3], [extensionattribute4], [extensionattribute5], [extensionattribute6], [TelephoneNumber], [mobile], [Manager], [ADsPath], [facsimileTelephoneNumber], [useraccountcontrol])
    EXEC (@query )

    set @rc = @@ROWCOUNT

    select @samAccountname = max(sAMAccountName) 
    from #users_ad

    select @query = 'SELECT objectGUID, employeeid, displayName, sn, givenName, company,Title, sAMAccountName, mail, pager, physicalDeliveryOfficeName, extensionattribute2, extensionattribute3, extensionattribute4, extensionattribute5, extensionattribute6, TelephoneNumber, mobile, Manager, ADsPath, facsimileTelephoneNumber, useraccountcontrol FROM ''''LDAP://DC=MORTON,DC=RU'''' WHERE objectCategory=''''User'''' and samAccountname > '''''+@samAccountname+''''' ORDER BY samAccountname'
    --PRINT @query 
    select @query = 'SELECT TOP 900 objectGUID, employeeid, displayName, sn, givenName, company,Title, sAMAccountName, mail, pager, physicalDeliveryOfficeName, extensionattribute2, extensionattribute3, extensionattribute4, extensionattribute5, extensionattribute6, TelephoneNumber, mobile, Manager, ADsPath, facsimileTelephoneNumber, useraccountcontrol FROM OPENQUERY(ADSI, ''' + @query + ''')'
    --PRINT @query 
END

programming/1c-bitrix/ldap-paged-select.txt · Последние изменения: 2017/03/27 11:26 — artur