Export Users And Roles in Ax through X++

Scenario 1

static void ExportUsersAndRoles(Args _args)

{

    SysExcelApplication      xlsApplication;

    SysExcelWorkBooks    xlsWorkBookCollection;

    SysExcelWorkBook     xlsWorkBook;

    SysExcelWorkSheets   xlsWorkSheetCollection;

    SysExcelWorkSheet    xlsWorkSheet;

    SysExcelRange            xlsRange;

    CustTable                   custTable;

    int                                row = 1;

    str                               fileName;

    SecurityUserRole            _SecurityUserRole;

    SecurityRoleTaskGrant       _SecurityRoleTaskGrant;

    SecurityTask                _SecurityTask;

    SecurityRole                _SecurityRole;

    UserInfo                    _UserInfo;

    //UserPermission              _UserPermission;

    ;

    fileName = "C:\\Users\\Uma Mahesh\\Desktop\\UsersAndRoles.xlsx";

    xlsApplication           = SysExcelApplication::construct();

    xlsWorkBookCollection    = xlsApplication.workbooks();

    xlsWorkBook              = xlsWorkBookCollection.add();

    xlsWorkSheetCollection   = xlsWorkBook.worksheets();

    xlsWorkSheet             = xlsWorkSheetCollection.itemFromNum(1);

    xlsWorkSheet.cells().item(row,1).value("User Role");

    xlsWorkSheet.cells().item(row,2).value("User Id");

    xlsWorkSheet.cells().item(row,3).value("Legal Entity");

    row++;

        while select _UserInfo

        {

            while select _SecurityUserRole where _SecurityUserRole.User==_UserInfo.id

            {

                    while select _SecurityRole where _SecurityRole.RecId==_SecurityUserRole.SecurityRole

                    {

                    xlsWorkSheet.cells().item(row,1).value(_SecurityRole.Name);

                    xlsWorkSheet.cells().item(row,2).value(_UserInfo.id);

                    xlsWorkSheet.cells().item(row,3).value(_UserInfo.company);

                    row++;

                    }

            }

        }

 

    if(WinApi::fileExists(fileName))

        WinApi::deleteFile(fileName);

    xlsWorkbook.saveAs(fileName);

    xlsApplication.visible(true);

}

Scenario 2

static void Export_Userbasedroles(Args _args)

{

    SysExcelApplication      xlsApplication;

    SysExcelWorkBooks    xlsWorkBookCollection;

    SysExcelWorkBook     xlsWorkBook;

    SysExcelWorkSheets   xlsWorkSheetCollection;

    SysExcelWorkSheet    xlsWorkSheet;

    SysExcelRange            xlsRange;

    CustTable                   custTable;

    int                         row = 1,n=2;

    str                         fileName;

    SecurityUserRole            _SecurityUserRole;

    SecurityRoleTaskGrant       _SecurityRoleTaskGrant;

    SecurityTask                _SecurityTask;

    SecurityRole                _SecurityRole;

    UserInfo                    _UserInfo;

    OMUserRoleOrganization      _OMUserRoleOrganization;

    OMInternalOrganization      _OMInternalOrganization;

    boolean check;

    ;

 

    fileName = "C:\\Users\\tectuser9\\Desktop\\Mahesh\\UsersAndRoles1.xlsx";

    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 Name");

    xlsWorkSheet.cells().item(row,3).value("User Role");

    xlsWorkSheet.cells().item(row,4).value("Legal Entity");

    xlsWorkSheet.cells().item(row,5).value("Entity Code");

 

    while select _SecurityRole

        join _SecurityUserRole  where _SecurityRole.RecId == _SecurityUserRole.SecurityRole

    {

        check = false;

        select * from _UserInfo where _UserInfo.id == _SecurityUserRole.User;

        while select _OMUserRoleOrganization where _OMUserRoleOrganization.User == _UserInfo.id && _OMUserRoleOrganization.SecurityRole == _SecurityRole.RecId

            join _OMInternalOrganization where  _OMUserRoleOrganization.OMInternalOrganization == _OMInternalOrganization.RecId

        {

            check = true;

            xlsWorkSheet.cells().item(row,1).value(_UserInfo.id);

            xlsWorkSheet.cells().item(row,2).value(_UserInfo.name);

            xlsWorkSheet.cells().item(row,3).value(_SecurityRole.Name);

            xlsWorkSheet.cells().item(row,4).value(_OMInternalOrganization.Name);

            xlsWorkSheet.cells().item(row,5).value(_OMInternalOrganization.getDataAreaId());

            n++;

        }

        if(!check)

        {

            xlsWorkSheet.cells().item(row,1).value(_UserInfo.id);

            xlsWorkSheet.cells().item(row,2).value(_UserInfo.name);

            xlsWorkSheet.cells().item(row,3).value(_SecurityRole.Name);

            xlsWorkSheet.cells().item(row,4).value(_OMInternalOrganization.Name);

            xlsWorkSheet.cells().item(row,5).value(_OMInternalOrganization.getDataAreaId());

            n++;

        }

      }

    if(WinApi::fileExists(fileName))

        WinApi::deleteFile(fileName);

    xlsWorkbook.saveAs(fileName);

    xlsApplication.visible(true);

 }