_Reference54 - таблица с отделами из 1С
param($department)
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = "Server=sql-1c8;database=1c82zp-db;uid=user1c8;pwd="
$connection.Open()
# Выбираем нужные данные
$sqlstr = "SELECT r54.[_Code], r54._Description, r54_t._Code, r54_t._Description FROM [1c82zp].[dbo].[_Reference54] r54
JOIN [1c82zp].[dbo].[_Reference54] r54_t on r54._ParentIDRRef = r54_t.[_IDRRef]
WHERE r54._Marked = 0
ORDER BY r54._Code";
$command = $connection.CreateCommand()
$command.CommandText = $sqlstr
$result = $command.ExecuteReader()
$result | %{
if ($_[1] -like "*$department*"){
write-host $_[0], $_[1], $_[2], $_[3]
}
$dep = $_[1] -replace "\s+$","" # убираем пробелы в конце
$cod = $_[0] -replace "\s+$","" # убираем пробелы в конце
$parent = $_[2] -replace "\s+$","" # убираем пробелы в конце
if ($parent){
$ado = Get-ADOrganizationalUnit -filter {admindescription -eq $parent} -SearchBase "OU=users,DC=local,DC=ru"
if (($ado | measure).count -eq 1){
$ado_ex = Get-ADOrganizationalUnit -filter {admindescription -eq $cod} -SearchBase $ado.DistinguishedName
if (($ado_ex | measure).count -eq 1){}
else {
try{
New-ADOrganizationalUnit -Name "$dep" -OtherAttributes @{"admindescription"=$cod} -Path $ado.DistinguishedName #-whatif
}
catch {
write-host "Не найден родитель: $cod (parent $parent) $dep " $error[0] -foreground red
}
}
}
}
else {
$ado_ex = Get-ADOrganizationalUnit -filter {admindescription -eq $cod} -SearchBase "OU=users,DC=local,DC=ru"
if (($ado_ex | measure).count -eq 1){}
else {
try {
New-ADOrganizationalUnit -Name "$dep" -OtherAttributes @{"admindescription"=$cod} -Path "OU=users,DC=local,DC=ru" -whatif
}
catch {}
}
}
}
$result.cLose();