[SQL] MQL\TCL script for Collaborative Space in Enovia

I'd like to make a filter for persons which are 'active' AND are in a specific collaborative space (namely Example_CS).

My script is a mix of MQL and TCL. My problem is that if I handle persons as business objects then the assigment declaration does not work: 0 result for assignments. If I handle persons as admin objects then I cannot execute it for more than one persons in one step (* wildcard). So both of my ideas do not work. My script is like the following. Could you take a look and suggest a solution?

Thank you

tcl;

cd {F:\SCRIPT\active_users_with_cs_and_access_roles}

eval {

set outputFileName "active_users_with_cs_and_access_roles.csv"
set outputFile [open $outputFileName w]


puts $outputFile "USER|EMAIL|SITE|Example_CS"

set userList [split [mql temp query bus Person * * where 'current==Active AND assignment==Example_CS' select id attribute\[Email Address\].value dump |] \n]
foreach userString $userList {
set userDetailsList [split $userString |]
set userName [lindex $userDetailsList 1]
set userEmail [lindex $userDetailsList 4]
set userId [lindex $userDetailsList 3]
set userSite [mql print person $userName select site dump]

set userRolesList [split [mql print person $userName select assignment dump |] |]

set userExampleRolesList {}


foreach role $userRolesList {
if { [string match "ctx::*" $role] } {

set userAccessRole [string trim [string map {"ctx::" ""} $role]]
set userAccessRoleElementsList [split $userAccessRole .]
set theRole [lindex $userAccessRoleElementsList 0]
set cs [lindex $userAccessRoleElementsList 1]
set csName [lindex $userAccessRoleElementsList 2]

if { $csName == "Example_CS" && $cs == "Example" } {
lappend userExampleRolesList $userAccessRole
}
}
}



set userExampleRolesList[join $userExampleRolesList ,]


puts $outputFile "$userName|$userEmail|$userSite|$userExampleRolesList|"
}

close $outputFile
}
exit

Continue reading...