-
Notifications
You must be signed in to change notification settings - Fork 1
/
persons.ps1
134 lines (115 loc) · 6.96 KB
/
persons.ps1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
function PerfromQuery($Query, $ConnectionString) {
try {
# Initialize connection and query information
# Connect to the SQL server
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection;
$SqlConnection.ConnectionString = $ConnectionString;
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand;
$SqlCmd.Connection = $SqlConnection;
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter;
#Query to get all person information adjust to liking#
$SqlCmd.CommandText = $query;
$SqlAdapter.SelectCommand = $SqlCmd;
$DataSet = New-Object System.Data.DataSet;
$SqlAdapter.Fill($DataSet) | out-null;
$sqlData = $DataSet.Tables[0];
return $sqlData | Select-Object -Property * -ExcludeProperty RowError, RowState, Table, ItemArray, HasErrors;
}
catch {
Write-Error "Something went wrong while connecting to the SQL server";
Write-Error $_.Exception.Message;
Exit;
}
}
$config = ConvertFrom-Json $configuration
$server = $config.server
$database = $config.database
$userId = $config.user
$password = $config.password
$connectionString = "Data Source=$server;Initial Catalog=$database;User Id=$userId;Password=$password;";
$languageId = "(1,2)"; # 1=NL, 2=EN
#Query to get all person information adjust to liking#
$personQuery = "SELECT [Medewerker] as [EmployeeId]
,[Nummer] as [Id]
,[Nummer] as [ExternalId]
,[FirstName] + ' ' + [LastName] + ' (' + [Medewerker] + ')' as [DisplayName]
,[Initials]
,[Nickname]
,[FirstName]
,[NamePrefix]
,[LastName]
,[Geboortedatum] as [DateOfBirth]
,[Geslacht_code] as [Sex]
,[Burgelijke_staat] as [MaritalStatus]
,[PartnerNamePrefix]
,[PartnerName]
,[Naamgebruik_code] as [NamingConvention]
,[Straat] as [Street]
,[Straat_toevoeging] as [StreetAddition]
,[Huisnummer] as [HouseNumber]
,[Huisnummer_toevoeging] as [HouseNumberAddition]
,[Postcode] as [PostalCode]
,[Woonplaats] as [City]
,[Land] as [County]
,[Mobiel_prive] as [MobilePhoneNumberPrivate]
,[Mobiel_werk] as [MobilePhoneNumberWork]
,[Telefoonnr_prive] as [PhoneNumberPrivate]
,[Telefoonnr_werk] as [PhoneNumberWork]
,[E-mail_prive] as [EmailAddressPrivate]
,[E-mail_werk] as [EmailAddressWork]
,[Geblokkeerd] as [IsBlocked]
,[Datum_in_dienst] as [EmploymentStartDate]
,[Datum_uit_dienst] as [EmploymentEndDate]
,[MultipleContracts] as [HasMultipleContracts]
FROM [dbo].[T4E_IAM_Persons] WHERE [lang_id] in $languageId
ORDER by [Medewerker] asc";
#Query to get all contract information adjust to liking#
$contractsQuery = "SELECT [Medewerker] as [EmployeeId]
,[Volgnummer_contract] as [Number]
,[Soort_medewerker] as [EmploymentType]
,[Aantal_FTE] as [FTE]
,[Begindatum_contract] as [StartDate]
,[Einddatum_contract] as [EndDate]
,[Werkgever] as [Employer]
,[Organisatorische_eenheid] as [DepartmentId]
,[Functie] as [FunctionId]
,[Kostenplaats] as [CostCenter]
,[Leidinggevende] as [PrimaryManagerId]
,[Vervangende_leidinggevende] as [SecondaryManagerId]
FROM [dbo].[T4E_IAM_Contracts] WHERE [lang_id] in $languageId"
#Query to get all function information adjust to liking#
$functionsQuery = "SELECT [Functie] as [Id]
,[Omschrijving] as [Description]
FROM [dbo].[T4E_IAM_OrganizationalFunctions] WHERE [lang_id] in $languageId"
#Query to get all department information adjust to liking#
$departmentsQuery = "SELECT [Organisatorische_eenheid] as [Id]
,[Omschrijving] as [DisplayName]
,[Omschrijving] as [Name]
,[Leidinggevende] as [ManagerId]
,[Parent] as [ParentDepartmentId]
FROM [dbo].[T4E_IAM_OrganizationalUnits] WHERE [lang_id] in $languageId"
$persons = PerfromQuery -Query $personQuery -ConnectionString $connectionString
#$personLookup = $persons | ForEach-Object { $_ } #copy persons
$contracts = PerfromQuery -Query $contractsQuery -ConnectionString $connectionString
$functions = PerfromQuery -Query $functionsQuery -ConnectionString $connectionString
$departments = PerfromQuery -Query $departmentsQuery -ConnectionString $connectionString
Foreach ($person in $persons) {
# Note that externalId and displayName are required to set. This is currently done in the query
# $person | Add-Member -Name "ExternalId" -MemberType NoteProperty -Value $person.Id -Force;
# $person | Add-Member -Name "DisplayName" -MemberType NoteProperty -Value "$($person.FirstName) $($person.LastName) ($($person.EmployeeId))" -Force;
#link contracts
[array]$contractsOfPerson = $($contracts | Where-Object { $_.EmployeeId -eq $person.EmployeeId })
if($null -eq $contractsOfPerson){$contractsOfPerson = @()}
$person | Add-Member -Name "Contracts" -MemberType NoteProperty -Value $contractsOfPerson -Force;
foreach ($contract in $person.Contracts) {
# Set special objects if needed for example: manager, department or function objects
#$contract | Add-Member -Name "PrimaryManager" -MemberType NoteProperty -Value $($personLookup | Where-Object { $_.EmployeeId -eq $contract.PrimaryManagerId }) -Force;
#$contract | Add-Member -Name "SecondaryManager" -MemberType NoteProperty -Value $($personLookup | Where-Object { $_.EmployeeId -eq $contract.SecondaryManagerId }) -Force;
$contract | Add-Member -Name "Department" -MemberType NoteProperty -Value $($departments | Where-Object { $_.Id -eq $contract.DepartmentId }) -Force;
#if($contract.Department) {
# $contract.Department | Add-Member -Name "Manager" -MemberType NoteProperty -Value $($personLookup | Where-Object { $_.EmployeeId -eq $contract.Department.ManagerId }) -Force;
#}
$contract | Add-Member -Name "Function" -MemberType NoteProperty -Value $($functions | Where-Object { $_.Id -eq $contract.FunctionId }) -Force;
}
Write-Output $person | ConvertTo-Json -Depth 10;
}