X++ Job to update worker contact information by excel upload

Upload a excel file to update the already existing Worker contact information

HR/Common/Workers/List Edit/Profile->Contact Information.

Note: In the Excel file all the columns should be formatted as Text and the fields should be given according to below header format.

PersonnelNumber, contactdescription, contacttype,contact,contactext,primarycontact,privatecontact
123,abc, email address, abcxyz@blogger.com, ,yes,no


static void EmployeeContactInfo(Args _args)
{
    SysExcelApplication application;
    SysExcelWorkbooks workbooks;
    SysExcelWorkbook workbook;
    SysExcelWorksheets worksheets;
    SysExcelWorksheet worksheet;
    SysExcelCells cells;
    COMVariantType type;
    System.DateTime ShlefDate;
    FilenameOpen filename,filenameopen;
    dialogField dialogFilename;
    Dialog dialog;

    LogisticsLocation logisticsLocation   ;
    LogisticsElectronicAddress logisticsElectronicAddress;
    DirPartyLocation dirPartyLocation;
    HcmWorker hcmWorker;
    DirPerson dirPerson;
    DirPartyTable dirPartyTable;

     LogisticsElectronicAddressMethodType logisticsElectronicAddressMethodType;
    NoYes noYes;
    int row=1;

    #Excel

    ;
    dialog = new Dialog("Excel Upoad");

    dialogFilename = dialog.addField(extendedTypeStr(FilenameOpen));
    dialog.filenameLookupFilter(["@SYS28576",#XLSX,"@SYS28576",#XLS]);
    dialog.filenameLookupTitle("Upload from Excel");
    dialog.caption("Excel Upload");
    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();
try
{


do
{

    row++;
    ttsBegin;
    select * from hcmWorker where hcmWorker.PersonnelNumber == cells.item(row,1).value().bStr();

    select * from dirPerson where dirPerson.RecId == hcmWorker.Person;
    select * from dirPartyTable where dirPartyTable.RecId == dirPerson.RecId;
    while select forupdate dirPartyLocation where dirPartyLocation.Party == dirPartyTable.RecId
    {

    select forupdate logisticsElectronicAddress where logisticsElectronicAddress.Location == dirPartyLocation.Location
       && logisticsElectronicAddress.Type == str2enum(logisticsElectronicAddressMethodType,cells.item(row,3).value().bStr());
        ttsBegin;
            if(logisticsElectronicAddress)
            {
                select forupdate logisticsLocation where  logisticsLocation.RecId == logisticsElectronicAddress.Location;

                logisticsLocation.Description  = cells.item(row,2).value().bStr();
                logisticsLocation.update();
                //logisticsElectronicAddress.Type = str2enum(logisticsElectronicAddressMethodType,cells.item(row,3).value().bStr());
                logisticsElectronicAddress.Locator = cells.item(row,4).value().bStr();

                if(LogisticsElectronicAddressMethodType::Phone == str2enum(logisticsElectronicAddressMethodType,cells.item(row,3).value().bStr()))
                {
                    logisticsElectronicAddress.LocatorExtension = cells.item(row,5).value().bStr();
                }

                logisticsElectronicAddress.validTimeStateUpdateMode(ValidTimeStateUpdate::CreateNewTimePeriod);
                logisticsElectronicAddress.update();
                dirPartyLocation.IsPrimary = str2enum(noYes,cells.item(row,6).value().bStr());
                dirPartyLocation.IsPrivate = str2enum(noYes,cells.item(row,7).value().bStr());
                dirPartyLocation.validTimeStateUpdateMode(ValidTimeStateUpdate::CreateNewTimePeriod);
                dirPartyLocation.update();
                info(strFmt("%1 --contact --%2 PN --",logisticsElectronicAddress.Locator,hcmWorker.PersonnelNumber));
                //info(strFmt("%1 --dis --%2 PN --",logisticsLocation.RecId,hcmWorker.PersonnelNumber ));
                //info(strFmt("%1-- private-- %2 --public--%3 PN --",enum2str(dirPartyLocation.IsPrivate) ,enum2str(dirPartyLocation.IsPrimary),hcmWorker.PersonnelNumber));
            }
        ttsCommit;


    }




    ttsCommit;
    type = cells.item(row+1, 1).value().variantType();
} while(type != COMVariantType::VT_EMPTY);

}

catch
{
    Error(strfmt("Upload Failed in row %1", row));
}
    info(strfmt("Employee Contact Information uploaded successfully"));
application.quit();
}