Emp Contract Master

static void EmpContractMaster(Args _args)
{
     #AviFiles
    FilenameOpen filename;
    dialogField dialogFilename,DialogJournalType;
    int jounaltype;
    Dialog dialog= new Dialog("Excel Upoad");
    Container excelCont[];
    GOD_PaymentMode PaymentType;
    int rowIdx;
    Counter linesImported;
    int lastRow,dimcount;
    boolean ok = true;
    Description c1,c3,c4,c5,c6,c7,c8,c9,c2,c10,c11,c12;
    //int64 c2;
    str input;
    container       accEntryPattern;
    BudgetType      budgetType;
    SysExcelApplication application;
    SysExcelWorkBooks workBooks;
    SysExcelWorkSheets workSheets;
    SysExcelWorkSheet workSheet;
    SysExcelCells cells;
    GOD_EmplContract    GOD_EmplContract;
    GOD_ContractValidity    GOD_ContractValidity;
    GOD_ContractType  GOD_ContractType;
    GOD_ContractNo  contractNo;
    HcmEmployment   hcmEmployment;
    NoYes NoYes;

    SysOperationProgress progress;
    struct      struct= new Struct();
    boolean                                 ret = true;
    dirPersonName dirPersonName;
    HcmWorker hcmworker;

    #define.CurrentVersion(1)
    #localmacro.CurrentList
    filename
    #endmacro

    #Excel
    #define.Star('*')
    #define.Space(' ')


    // convert into str from excel cell value
    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 "";
    }

    // Find last row from excel

    int findLastRow(SysExcelWorkSheet _workSheet)
    {
        SysExcelRange range;
        ;

        range = _workSheet.cells().range(#ExcelTotalRange);

        try
        {
            // Finds the row where the first contents is found.
            range = range.find(#Star, null, #xlFormulas, #xlWhole, #xlByRows, #xlPrevious);
        }
        catch (Exception::Error)
        {
            error("@SYS59926");
            return 0;
        }

        if (range)
        {
            return range.row();
        }
        else
        {
            return 0;
        }
    }


    ;


    dialogFilename = dialog.addField(extendedtypestr(FilenameOpen));
   // DialogJournalType = dialog.addField(enumstr(God_JournalToUpload),"Journal Type");
    dialog.filenameLookupFilter(["@SYS28576",#XLS,#Xlsx]);
    dialog.filenameLookupTitle("Upload from Excel");
    dialogFilename.value(filename);

    if(!dialog.run())
    return;

    filename = dialogFilename.value();

   // ttsbegin;

    application = SysExcelApplication::construct();
    workBooks = application.workbooks();
    workBooks.open(filename,0,true);

    workSheets = workBooks.item(1).worksheets();
    // this.importExcel("Sheet1");

    input = "Sheet1";
    workSheet = workSheets.itemFromNum(1);//.itemFromName(input);
    cells = workSheet.cells();
    lastRow = findLastRow(workSheet);
    rowIdx = 2;
    progress = new SysOperationProgress();
    progress.setCaption("Excel Importing");
    progress.setTotal(lastRow);
    progress.setAnimation(#AviTransfer);
    setprefix("Excel Import");
    while (rowIdx <= lastRow)
    {

        setPrefix(strfmt("Excel Row: %1", rowIdx));

        c1 = COMVariant2Str(cells.item(rowIdx,1).value());
        c2 = COMVariant2Str(cells.item(rowIdx,2).value());
        c3 = COMVariant2Str(cells.item(rowIdx,3).value());
        c4 = COMVariant2Str(cells.item(rowIdx,4).value());
        c5 = COMVariant2Str(cells.item(rowIdx,5).value());
        c6 = COMVariant2Str(cells.item(rowIdx,6).value());
        c7 = COMVariant2Str(cells.item(rowIdx,7).value());
        c8 = COMVariant2Str(cells.item(rowIdx,8).value());
        c9 = COMVariant2Str(cells.item(rowIdx,9).value());
        c10 = COMVariant2Str(cells.item(rowIdx,10).value());
        c11 = COMVariant2Str(cells.item(rowIdx,11).value());
        c12 = COMVariant2Str(cells.item(rowIdx,11).value());

        linesImported++;

        //ttsBegin;
        hcmworker = HcmWorker::findByPersonnelNumber(c1);
        select firstonly hcmEmployment where hcmEmployment.Worker == hcmworker.RecId;
        contractNo = str2int(c2);

        if(HcmWorker)
        {
            ttsBegin;
            select forUpdate GOD_EmplContract
            where GOD_EmplContract.EmplId == hcmworker.PersonnelNumber
               && GOD_EmplContract.ContractNo == contractNo && GOD_EmplContract.Active == NoYes::Yes;
            if(GOD_EmplContract)
            {
                GOD_EmplContract.ContractNo = contractNo;
                GOD_EmplContract.GradeId = c3;
                GOD_EmplContract.GradeLevelId  = c4;
                GOD_EmplContract.BasicSalary = any2real(c5);
                GOD_EmplContract.PaymentType = str2enum(PaymentType,c6);
                GOD_EmplContract.OrganizationUnitId = c7;
                GOD_EmplContract.OMCostCenter= c8;
                GOD_EmplContract.EmployeeType = str2enum(GOD_ContractType,c9);
                GOD_EmplContract.Validity = str2enum(GOD_ContractValidity,c10);
                GOD_EmplContract.ProbationCompleted = str2enum(NoYes,c11);
                GOD_EmplContract.ProfileGroupId = c12;
                GOD_EmplContract.StartDate = any2date(hcmEmployment.ValidFrom);
                GOD_EmplContract.EffectiveDate = any2date(hcmEmployment.ValidFrom);
                GOD_EmplContract.update();
            }
            else
            {
                GOD_EmplContract.ContractNo = contractNo;
                GOD_EmplContract.GradeId = c3;
                GOD_EmplContract.GradeLevelId  = c4;
                GOD_EmplContract.BasicSalary = any2real(c5);
                GOD_EmplContract.PaymentType = str2enum(PaymentType,c6);
                GOD_EmplContract.OrganizationUnitId = c7;
                GOD_EmplContract.OMCostCenter = c8;
                GOD_EmplContract.EmployeeType = str2enum(GOD_ContractType,c9);
                GOD_EmplContract.Validity = str2enum(GOD_ContractValidity,c10);
                GOD_EmplContract.ProbationCompleted = str2enum(NoYes,c11);
                GOD_EmplContract.ProfileGroupId = c12;
                GOD_EmplContract.EmplId = hcmworker.PersonnelNumber;
                GOD_EmplContract.Active = NoYes::Yes;
                GOD_EmplContract.StartDate = any2date(hcmEmployment.ValidFrom);
                GOD_EmplContract.EffectiveDate = any2date(hcmEmployment.ValidFrom);
                GOD_EmplContract.insert();
            }
            ttsCommit;
        }

        progress.setText("Importing " + c1);
        progress.setCount(linesImported);
        rowIdx++;
     }

     info("done");
}