Finance | Project Operations, Human Resources, ...

Duties and Privilege's are not going to be retrieved

(0) Share

Report

Posted on by Shabir Ahmad

3,110

static void Mzk_ExportUsersecurities_User123(Args _args)
{
SysExcelApplication xlsApplication;
SysExcelWorkBooks xlsWorkBookCollection;
SysExcelWorkBook xlsWorkBook;
SysExcelWorkSheets xlsWorkSheetCollection;
SysExcelWorkSheet xlsWorkSheet;
SysExcelRange xlsRange;
CustTable custTable;
int row = 1;
str fileName;

SecurityTask securityTask, securityTaskDuty, securityTaskPriv;
SecuritySubTask securitySubTask;
SecurityRoleTaskGrant securityRoleTaskGrant;
SecurityRole securityRole, securityRoleDuty,securityRolePriv, securityRoleM;
SecurityTaskEntryPoint SecurityTaskEntryPoint;
SecurableObject SecurableObject;
SecurityUserRole securityUserRole;
UserInfo userInfo;

SecurityTaskEntryPoint taskEntryPoint;
SecurityRole role;
SecurityRoleTaskGrant taskGrant;
SecuritySubTask subTask;
SecurityTask privilege;

str privName;
str dutyName;
str entrName;
str accessLevel;

boolean i = true,j = true;
;
fileName = "C:\\Temp\\AXrole.csv"; //Rename file
xlsApplication = SysExcelApplication::construct();
xlsWorkBookCollection = xlsApplication.workbooks();
xlsWorkBook = xlsWorkBookCollection.add();
xlsWorkSheetCollection = xlsWorkBook.worksheets();
xlsWorkSheet = xlsWorkSheetCollection.itemFromNum(1);
xlsWorkSheet.cells().item(row,1).value("User Id");
xlsWorkSheet.cells().item(row,2).value("User Role");
xlsWorkSheet.cells().item(row,3).value("Duty");
xlsWorkSheet.cells().item(row,4).value("Privilege AOT");
xlsWorkSheet.cells().item(row,5).value("Privilege Description");
xlsWorkSheet.cells().item(row,6).value("Entry Point");
xlsWorkSheet.cells().item(row,7).value("Permission");

row++;

while select * from securityRole
join securityUserRole
where securityUserRole.User like "USERNAME" //Enter user information here
&& securityUserRole.SecurityRole == securityRole.RecId
{
while select * from securityRoleTaskGrant
join * from securityTask
where securityRoleTaskGrant.SecurityRole == securityRole.RecId
&& securityTask.RecId == securityRoleTaskGrant.SecurityTask

{
if(securityTask.Type == SecurityTaskType::Duty)
{
while select SecurityTaskEntryPoint
join SecurableObject where SecurableObject.RecId == SecurityTaskEntryPoint.EntryPoint
join subTask where subTask.SecuritySubTask == SecurityTaskEntryPoint.SecurityTask
join taskGrant where taskGrant.SecurityTask == subTask.SecurityTask
join role where role.RecId == taskGrant.SecurityRole
&& role.AotName like securityRole.AotName

{
select privilege
where privilege.RecId == taskGrant.SecurityTask
&& SecurityTaskType::Duty == privilege.Type;
dutyName = privilege.AotName;

select privilege
where privilege.RecId == subTask.SecuritySubTask
&& SecurityTaskType::Privilege == privilege.Type;
privName = privilege.AotName;
{
//Information to Excel
xlsWorkSheet.cells().item(row,1).value(securityUserRole.User); //User id
xlsWorkSheet.cells().item(row,2).value(securityRole.Name); //Role Name
xlsWorkSheet.cells().item(row,3).value(dutyName); //DutyName
xlsWorkSheet.cells().item(row,4).value(privName); //Privilege AOT Name
xlsWorkSheet.cells().item(row,5).value(privilege.Name); //Privilege Description
xlsWorkSheet.cells().item(row,6).value(SecurableObject.Name); //Entry Points
xlsWorkSheet.cells().item(row,7).value(enum2str(SecurityTaskEntryPoint.PermissionGroup)); //Access Permission level
row++;
}
}
}
else //Role if contain Privilegs directly
{
while select * from SecurityTaskEntryPoint
join SecurableObject
where SecurableObject.RecId == SecurityTaskEntryPoint.EntryPoint
&& SecurityTaskEntryPoint.SecurityTask == securityTask.RecId
{
xlsWorkSheet.cells().item(row,1).value(securityUserRole.User); //User id
xlsWorkSheet.cells().item(row,2).value(securityRole.Name); //Role Name
xlsWorkSheet.cells().item(row,3).value(""); //DutyName
xlsWorkSheet.cells().item(row,4).value(securityTask.AotName); //Privilege AOT Name
xlsWorkSheet.cells().item(row,5).value(securityTask.Name); //Privilege Description
xlsWorkSheet.cells().item(row,6).value(SecurableObject.Name); //Entry Points
xlsWorkSheet.cells().item(row,7).value(enum2str(SecurityTaskEntryPoint.PermissionGroup)); //Access Permission level
row++;
}
}
}
}
if(WinApi::fileExists(fileName))
WinApi::deleteFile(fileName);
xlsWorkbook.saveAs(fileName);
xlsApplication.visible(true);
info(strFmt("File has been exported"));
}

Hi Experts,

After adding this logic i get only Header not rolese and Privileges as shown in exported excel sheet image. Note I used this code before but it was working right now not..

Best Regards,

Shabir Ahmad

I have the same question (0)

All responses (33)

Answers (0)

Suggested answer

nmaenpaa 101,160 Moderator on at

Like (1)

Report

Hi Shabir,

When you share code, could you please click "Use rich text formatting" -> "Insert" -> "Code" to make the code easier to read.

Which version are you working with? AX2012 or D365?

You wrote that after adding some logic it stopped working. Is this correct? I suggest you to compare the old code (that was working) and the new code (that is not working) and find the issue. We can also help in finding the issue if you share us both codes. But right now we can't see what was the old and new code, and what was changed, so it's not so easy for us to spot what was wrong in your change.

I also can warmly recommend using the debugger to understand what your code does. This way you will get useful information that helps you pinpoint the issue.

Was this reply helpful? Yes No
André Arnaud de Cal... 301,075 Super User 2025 Season 2 on at

Like (0)

Report

Looking at the Excel interface coding, it should be AX 2012. I do assume you replaced "USERNAME" with a valid value? Otherwise, follow the suggestions from Nikolaos.

Was this reply helpful? Yes No
Blue Wang on at

Like (1)

Report

Hi Shabir,

Your code is exactly the same as this : erpdax.wordpress.com/.../

So you must debug code , which condition is not met or where the value is empty, share the screenshot.

Was this reply helpful? Yes No
Shabir Ahmad 3,110 on at

Like (0)

Report

Hi Blue Wang,

Thanks, Yes, this is the blog erpdax.wordpress.com/.../ from wher i took the code before copy and paste this code it was working now not working. (My Requirement is to get Duties and Privilege's of all users. If you know any other site.??)

Best Regards,

Shabir Ahmad

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report
I'm sure something has changed if it's not working anymore, and was working before. Could you check from your version control what changes have been made? Then you can either revert those changes or try to fix the issue.

Anyway, as Andre pointed out, you should at least adjust the user id condition - assuming it's your real code.

where securityUserRole.User like "USERNAME" //Enter user information here

Was this reply helpful? Yes No
Blue Wang on at

Like (1)

Report

Hi Shabir,

There is another blog here, but I haven't tried it. It seems to be based on a role.

anithasantosh.wordpress.com/.../

Was this reply helpful? Yes No
Shabir Ahmad 3,110 on at

Like (0)

Report

Hi Blue Wang,
I run the code and i faced an error as shown in the image.

Best Regards,
Shabir Ahmad

Was this reply helpful? Yes No
André Arnaud de Cal... 301,075 Super User 2025 Season 2 on at

Like (0)

Report

Hi Shabir,

How and where do you set the file name? Is the file location accessible? Note that you started your question with an Excel interface, now you are changing the topic to a CSV file. Did you already found the culprit about your original issue?

Was this reply helpful? Yes No
Blue Wang on at

Like (1)

Report

Hi Shabir,

Do you have set your permissions, you have initialized the file, set it ready for writing etc ?

You must make sure that AOS service account has permissions to create a file .

Code run on server or client?

Was this reply helpful? Yes No

faiz7049

2,382 on at

Like (1)

Report

Hi Ahmed,

I saw your X code

while select * from securityRole
join securityUserRole
where securityUserRole.User like "USERNAME" //Enter user information here
&& securityUserRole.SecurityRole == securityRole.RecId

Actually Alias, User ID and User name are not same as given below:

3632.User.JPG

User asterisk(*) after name in code.

while select * from securityRole
            join securityUserRole
            where securityUserRole.User like 'fahad*'  //Enter user information here
            && securityUserRole.SecurityRole == securityRole.RecId

After you will get result.

2262.Result.JPG

whole job code is below

static void Mzk_ExportUsersecurities_User(Args _args)
{
    SysExcelApplication         xlsApplication;
    SysExcelWorkBooks           xlsWorkBookCollection;
    SysExcelWorkBook            xlsWorkBook;
    SysExcelWorkSheets          xlsWorkSheetCollection;
    SysExcelWorkSheet           xlsWorkSheet;
    SysExcelRange               xlsRange;
    CustTable                   custTable;
    int                         row = 1;
    str                         fileName;

    SecurityTask                securityTask, securityTaskDuty, securityTaskPriv;
    SecuritySubTask             securitySubTask;
    SecurityRoleTaskGrant       securityRoleTaskGrant;
    SecurityRole                securityRole, securityRoleDuty,securityRolePriv, securityRoleM;
    SecurityTaskEntryPoint      SecurityTaskEntryPoint;
    SecurableObject             SecurableObject;
    SecurityUserRole            securityUserRole;
    UserInfo                    userInfo;

    SecurityTaskEntryPoint  taskEntryPoint;
    SecurityRole            role;
    SecurityRoleTaskGrant   taskGrant;
    SecuritySubTask         subTask;
    SecurityTask            privilege;

    str privName;
    str dutyName;
    str entrName;
    str accessLevel;

    boolean i = true,j = true;
    ;
    fileName = "C:\\Test/username.csv";    //Rename file
    xlsApplication           = SysExcelApplication::construct();
    xlsWorkBookCollection    = xlsApplication.workbooks();
    xlsWorkBook              = xlsWorkBookCollection.add();
    xlsWorkSheetCollection   = xlsWorkBook.worksheets();
    xlsWorkSheet             = xlsWorkSheetCollection.itemFromNum(1);
    xlsWorkSheet.cells().item(row,1).value("User Id");
    xlsWorkSheet.cells().item(row,2).value("User Role");
    xlsWorkSheet.cells().item(row,3).value("Duty");
    xlsWorkSheet.cells().item(row,4).value("Privilege AOT");
    xlsWorkSheet.cells().item(row,5).value("Privilege Description");
    xlsWorkSheet.cells().item(row,6).value("Entry Point");
    xlsWorkSheet.cells().item(row,7).value("Permission");

    row  ;

    while select * from securityRole
            join securityUserRole
            where securityUserRole.User like 'fahad*'  //Enter user information here
            && securityUserRole.SecurityRole == securityRole.RecId
        {
        while select * from securityRoleTaskGrant
        join * from securityTask
            where securityRoleTaskGrant.SecurityRole == securityRole.RecId
                && securityTask.RecId == securityRoleTaskGrant.SecurityTask

        {
            if(securityTask.Type == SecurityTaskType::Duty)
            {
               while select SecurityTaskEntryPoint
                            join SecurableObject where SecurableObject.RecId == SecurityTaskEntryPoint.EntryPoint
                            join subTask where subTask.SecuritySubTask == SecurityTaskEntryPoint.SecurityTask
                            join taskGrant where taskGrant.SecurityTask == subTask.SecurityTask
                            join role where role.RecId == taskGrant.SecurityRole
                                &&  role.AotName like securityRole.AotName

                {
                    select privilege
                        where privilege.RecId == taskGrant.SecurityTask
                        && SecurityTaskType::Duty == privilege.Type;
                            dutyName = privilege.AotName;

                    select privilege
                        where privilege.RecId == subTask.SecuritySubTask
                        && SecurityTaskType::Privilege == privilege.Type;
                        privName = privilege.AotName;
                    {
                       //Information to Excel
                        xlsWorkSheet.cells().item(row,1).value(securityUserRole.User);         //User id
                        xlsWorkSheet.cells().item(row,2).value(securityRole.Name);  //Role Name
                        xlsWorkSheet.cells().item(row,3).value(dutyName); //DutyName
                        xlsWorkSheet.cells().item(row,4).value(privName);  //Privilege AOT Name
                        xlsWorkSheet.cells().item(row,5).value(privilege.Name);  //Privilege Description
                        xlsWorkSheet.cells().item(row,6).value(SecurableObject.Name);  //Entry Points
                        xlsWorkSheet.cells().item(row,7).value(enum2str(SecurityTaskEntryPoint.PermissionGroup));  //Access Permission level
                        row  ;
                    }
                }
            }
            else             //Role if contain Privilegs directly
            {
                while select * from SecurityTaskEntryPoint
                            join SecurableObject
                            where SecurableObject.RecId == SecurityTaskEntryPoint.EntryPoint
                            && SecurityTaskEntryPoint.SecurityTask == securityTask.RecId
                {
                    xlsWorkSheet.cells().item(row,1).value(securityUserRole.User);         //User id
                    xlsWorkSheet.cells().item(row,2).value(securityRole.Name);  //Role Name
                    xlsWorkSheet.cells().item(row,3).value(""); //DutyName
                    xlsWorkSheet.cells().item(row,4).value(securityTask.AotName);  //Privilege AOT Name
                    xlsWorkSheet.cells().item(row,5).value(securityTask.Name);  //Privilege Description
                    xlsWorkSheet.cells().item(row,6).value(SecurableObject.Name);  //Entry Points
                    xlsWorkSheet.cells().item(row,7).value(enum2str(SecurityTaskEntryPoint.PermissionGroup));  //Access Permission level
                    row  ;
                }
            }
        }
    }
    if(WinApi::fileExists(fileName))
    WinApi::deleteFile(fileName);
    xlsWorkbook.saveAs(fileName);
    xlsApplication.visible(true);
    info(strFmt("File has been exported"));
}

Please let me know if you have any query.

Best Regard,

Syed Faiz Abbas

Please take time to click 'Yes' on the answers that helped you guide in the right direction to help other community members.