Export to excel sheet in AX

void buttonclicked()
{
     Args                args;
    ReportRun           reportRun;

    Container           con;

    ;
    args = new Args();


    con = conins(con,1,ProjIdFilter.text());
    con = conins(con,2,PeriodNumber.valueStr());

    args.parmObject(NEW containerClass(con));
    new MenuFunction(identifierstr(TimeSheetAnalysisExcel), MenuItemType::Action).run(args);



}

download:
https://drive.google.com/file/d/0ByF76Avzw0r1cW0wNGZKUlVDdFE/view?usp=sharing

Import from Excel sheet to AX

public   void insertEmplIqamaIdentificationTable()
{
    SysExcelApplication             application;
    SysExcelWorkbooks               workbooks;
    SysExcelWorkbook                workbook;
    SysExcelWorksheets              worksheets;
    SysExcelWorksheet               worksheet;
    SysExcelCells                   cells;
    COMVariantType                  type;
    System.DateTime                 ShlefDate;
    FilenameOpen                    filename;
    dialogField                     dialogFilename;
    Dialog                          dialog;
    int row=0;
    #Excel
    SysOperationProgress            progress;
    #AVIFiles

    HRMEmplIdentification   HRMEmplIdentification,findIdentification,checkrecordexitsornot,checkpassportdetailsexitsornot;
    HRMIdentification       IqamaIdentification,passIdentification;
    PAYFDateHijri           ValidUnTillIqamaDateHijri,ActualIqamaDateHijri,ValidUnTillPassDateHijri,ActualPassDateHijri;
    HRMIdentificationTypeId IdentificationTypeId;
    Emplid                  emplid;
    TransDate               ValidUntiliqamaDate,ValidUntilpassDate;
    PAYFEmplIdentificationGroup IdentificationGroup;
    PAYFEmplIdentificationId    IdentificationId;
    Projid                      projid;
    Empltable   Empltable;




    str COMVariant2Str(COMVariant _cv, int _decimals = 0, int _characters = 0, int _separator1 = 0, int _separator2 = 0)
    {
        switch (_cv.variantType())
    {
        case (COMVariantType::VT_BSTR):
            return _cv.bStr();

        case (COMVariantType::VT_R4):
            return num2str(_cv.float(),_characters,_decimals,_separator1,_separator2);

        case (COMVariantType::VT_R8):
            return num2str(_cv.double(),_characters,_decimals,_separator1,_separator2);

        case (COMVariantType::VT_DECIMAL):
            return num2str(_cv.decimal(),_characters,_decimals,_separator1,_separator2);

        case (COMVariantType::VT_DATE):
            return date2str(_cv.date(),123,2,1,2,1,4);

        case (COMVariantType::VT_EMPTY):
            return "";

        default:
            throw error(strfmt("@SYS26908", _cv.variantType()));
    }
        return "";
    }

;
    Progress = new SysOperationProgress();
    Progress.setCaption("Uploading Employee  Details");
    Progress.setAnimation(#AviStopWatch);

    dialog              =   new Dialog("Excel Upload Details");
    dialogFilename      =   dialog.addField(typeId(FilenameOpen));
    dialog.filenameLookupFilter(["@SYS28576",#XLSX,"@SYS28576",#XLS]);
    dialog.filenameLookupTitle("Upload from Excel");
    dialog.caption("Excel Upload Employee  Details");
    dialogFilename.value(filename);
    if(!dialog.run())
    return;
    filename            =   dialogFilename.value();
    application         =   SysExcelApplication::construct();
    workbooks           =   application.workbooks();

    try
    {
        workbooks.open(filename);
    }
    catch (Exception::Error)
    {
        throw error("File cannot be opened.");
    }
    workbook            =   workbooks.item(1);
    worksheets          =   workbook.worksheets();
    worksheet           =   worksheets.itemFromNum(1);
    cells               =   worksheet.cells();

     do
    {
        try
        {
            ttsbegin;
            row++;
            IqamaIdentification         = COMVariant2Str(cells.item(row+1, 9).value());
            ValidUnTillIqamaDateHijri   = COMVariant2Str(cells.item(row+1, 2).value());
         
           ValidUntiliqamaDate         = global::toGregorianDateNew(ValidUnTillIqamaDateHijri);
            ActualIqamaDateHijri        = CalendarDateConvert::construct().toHijriDateOld(ValidUntiliqamaDate);

            ValidUnTillPassDateHijri    = COMVariant2Str(cells.item(row+1, 3).value());
       
          ValidUntilpassDate          = global::toGregorianDateNew(ValidUnTillPassDateHijri);

             ActualPassDateHijri         = ValidUnTillPassDateHijri;
            passIdentification          = COMVariant2Str(cells.item(row+1, 4).value());

            if(IqamaIdentification)
            {
                Empltable = empltable::findEmplIqamaNo(IqamaIdentification,true);

                if(Empltable.RecId != 0)
                {
                    checkrecordexitsornot = HRMEmplIdentification::findEmplIdentBytypeId(Empltable.EmplId,"Iqama",true);

                    if(checkrecordexitsornot.RecId == 0 || checkrecordexitsornot.ValidUntilDateHijri != ActualIqamaDateHijri)
                    {
                        HRMEmplIdentification.Identification = IqamaIdentification;
                        HRMEmplIdentification.ValidUntilDate   = ValidUntiliqamaDate;
                        HRMEmplIdentification.ValidUntilDateHijri =  ActualIqamaDateHijri;

                        HRMEmplIdentification.EmplId =Empltable.EmplId;
                        HRMEmplIdentification.ProjId =EMpltable.Dimension[2];
                        HRMEmplIdentification.IdentificationTypeId = "Iqama";

                        HRMEmplIdentification.IdentificationGroup =PAYFEmplIdentificationGroup::Employee;
                        HRMEmplIdentification.IdentificationId =Empltable.EmplId;
                        HRMEmplIdentification.insert();

                     }


                  //check passport details exits or not in HRMemplIdentification table
                    checkpassportdetailsexitsornot = HRMEmplIdentification::findEmplIdentBytypeId(Empltable.EmplId,"Passport",true);
                    if(checkpassportdetailsexitsornot.RecId != 0 && checkpassportdetailsexitsornot.ValidUntilDateHijri == ActualPassDateHijri
                    && checkpassportdetailsexitsornot.Identification == passIdentification )
                    {
                    checkpassportdetailsexitsornot.Identification = passIdentification;
                    checkpassportdetailsexitsornot.ValidUntilDate   = ValidUntilpassDate;
                    checkpassportdetailsexitsornot.ValidUntilDateHijri =  ActualPassDateHijri;

                    checkpassportdetailsexitsornot.EmplId =Empltable.EmplId;
                    checkpassportdetailsexitsornot.ProjId =EMpltable.Dimension[2];
                    checkpassportdetailsexitsornot.IdentificationTypeId = "Passport";

                    checkpassportdetailsexitsornot.IdentificationGroup =PAYFEmplIdentificationGroup::Employee;
                    checkpassportdetailsexitsornot.IdentificationId =Empltable.EmplId;
                    checkpassportdetailsexitsornot.doUpdate();
                    }
                    else
                    {
                    checkpassportdetailsexitsornot.Identification = passIdentification;
                    checkpassportdetailsexitsornot.ValidUntilDate   = ValidUntilpassDate;
                    checkpassportdetailsexitsornot.ValidUntilDateHijri =  ActualPassDateHijri;

                    checkpassportdetailsexitsornot.EmplId =Empltable.EmplId;
                    checkpassportdetailsexitsornot.ProjId =EMpltable.Dimension[2];
                    checkpassportdetailsexitsornot.IdentificationTypeId = "Passport";

                    checkpassportdetailsexitsornot.IdentificationGroup =PAYFEmplIdentificationGroup::Employee;
                    checkpassportdetailsexitsornot.IdentificationId =Empltable.EmplId;
                    checkpassportdetailsexitsornot.doinsert();
                    }
                  }
                  else
                  {
                  Error(strfmt("Upload Failed of iqama No %1 ,because employee details does not exits in employee master table.",IqamaIdentification));
                  }
            }
            type = cells.item(row+2, 1).value().variantType();
            progress.setText(strfmt("Uploading Employee New Iqama / passport Details %1",row+1));
            ttscommit;
        }

        catch
        {
            Error(strfmt("Upload Failed in row %1 iqama No %2", row+1,IqamaIdentification));
        }

    }
    while (type != COMVariantType::VT_EMPTY );
    info(strfmt("Employee New Iqama Details uploaded successfully"));
    progress.kill();
    application.quit();


}

Convert Hijri To Gregorian in ax 2009

date toGregorianDateNew(PAYFDateHijri _hijriDate)
{
    date                ret;
    str                 retStr;
    HijriToGregorian.HijriToGregorian convert;
    CLRObject           clr;        // Define a CLRObject object.
     InteropKind                         interopKind;
   ;
    clr = new HijriToGregorian.HijriToGregorian();
    convert = clr;

    retStr = convert.HijriToGreg(_hijriDate);

    ret = str2date(retstr,321);
    return ret;
}


example :

 toGregorianDateNew("1460/05/13");

Download:

https://drive.google.com/file/d/0ByF76Avzw0r1eFVSUEpiRk9Kdms/view?usp=sharing

Check user group history In AX

static void checkUserGroup(Args _args)
{
    UserGroupList           UserGroupList;
     UserInfo    UserInfo;
    ;

    while select UserGroupList
    where UserGroupList.groupId == "UsergroupId"
    {
        info(strfmt('|  Users - %1     |   modifiedBy - %2     |   modify datetime - %3    |', UserGroupList.userId,UserGroupList.createdBy,UserGroupList.createdDateTime));
    }


}

Copy User group Record level security to another user group In AX

static void AXCopyUsergroupRLS(Args _args)
{
    SysRecordLevelSecurity  SysRecordLevelSecurity,insertRLS;
    dialogfield srcdf,destdf;
    dialog dialog;
    userGroupid src,dest;
    ;
    dialog = new dialog("copying user group's Record Level security ");
    srcdf = dialog.addField(typeid(userGroupid),"Source");
    destdf = dialog.addField(typeid(userGroupid),"Destination");

     if(dialog.run())
    {
        src = srcdf.value();
        dest = destdf.value();
        if(src!="" && dest !="")
        {
          ttsbegin;
          info("Copying RLS to destination user group");
          while select * from SysRecordLevelSecurity
            where SysRecordLevelSecurity.groupId == src
            {

            insertRLS.data(SysRecordLevelSecurity);
            insertRLS.groupId = dest;
            insertRLS.insert();

            }
             ttscommit;
            info("User groups RLS has been copied!");
        }
     }
     else
    {
        info("Canceled by user");
    }



}

Copy one user permissions group to another user in AX

static void AXcopyUserGroup(Args _args)
{
    usergrouplist usergrouplist,ins;
    userid src,dest;
    dialogfield srcdf,destdf;
    dialog dialog;
    ;
    dialog = new dialog("copying user's user groups");
    srcdf = dialog.addField(typeid(userid),"Source");
    destdf = dialog.addField(typeid(userid),"Destination");



    if(dialog.run())
    {
        src = srcdf.value();
        dest = destdf.value();
        if(src!="" && dest !="")
        {
            ttsbegin;
            while select * from usergrouplist
            where usergrouplist.userId == dest
            {
                info(strfmt("Before update: '%1'-'%2'",usergrouplist.userid,usergrouplist.groupId));
            }

            info("Deleting user groups from destination user");
            delete_from usergrouplist
                where  usergrouplist.userId == dest;


            info("Copying user groups to destination user");
            while select * from usergrouplist
            where usergrouplist.userId == src
            {
                info(strfmt("source user: '%1'-'%2'",usergrouplist.userid,usergrouplist.groupId));
                ins.clear();
                ins.data(usergrouplist);
                ins.UserId = dest;
                ins.insert();
            }
            ttscommit;
            info("User groups has been copied!");
        }
    }
    else
    {
        info("Canceled by user");
    }


}

Copy one UserGroup Users list to another user group in AX

static void AXcopyUserGroupUserlist(Args _args)
{
    usergrouplist usergrouplist,ins,exitslist;
    userGroupid src,dest;
    dialogfield srcdf,destdf;
    dialog dialog;
    ;
    dialog = new dialog("copying user group's user lists ");
    srcdf = dialog.addField(typeid(userGroupid),"Source");
    destdf = dialog.addField(typeid(userGroupid),"Destination");



    if(dialog.run())
    {
        src = srcdf.value();
        dest = destdf.value();
        if(src!="" && dest !="")
        {
            ttsbegin;


            info("Copying user groups USER ID to destination user GROUP ");
            while select * from usergrouplist
            where usergrouplist.groupId == src
            {
                select exitslist where exitslist.UserId == usergrouplist.userId
                && exitslist.groupId == dest;

                if(exitslist.RecId == 0)
                {
                ins.clear();
                ins.userId = usergrouplist.userId;
                ins.groupId = dest;
                ins.insert();
                }

            }
            ttscommit;
            info("User groups has been copied!");
        }
    }
    else
    {
        info("Canceled by user");
    }


}