Home > Computer Programming, dynamics ax 2009, dynamics ax 2012 > Import data prospect with address and phone to ax 2012 database

Import data prospect with address and phone to ax 2012 database


continuing from last post, I’ve just added a small custom, so we can upload the address and the contact data

please modified for your own needs

//created by ronny 2014-05-16
//upload prospect from xls file

static void HPG_ImportProspectsver2(Args _args)
{
    //inisialisasi file xlsx
    Filename FileNamePath  = 'C:\\upload\\prospect-20140513.xlsx';

    #WinApi
    #File

    //inisialisasi object xlsx
    SysExcelApplication application = SysExcelApplication::construct();
    SysExcelWorkbooks   workbooks   = application.workbooks();
    SysExcelWorkbook    workbook;
    SysExcelWorksheets  workSheets;
    SysExcelWorksheet   workSheet;
    SysExcelCells       cells;
    SysExcelCell        cell;

    //inisialisasi variable
    int                 i=0, j=0, k=0;
    Filename            Filename;
    int                 temporary=1;
    int                 ii;
    boolean caninsert;
    boolean _isPerson;
    str tDate;
    str tTime;
    str dicky;

    //inisialisasi table
    DirPartyTable   party;
    DirPersonName   person;
    DirPerson       pers;
    smmbusreltable  summbus;
    
    //function
    //LogisticsElectronicAddressEntity logistik = new LogisticsElectronicAddressEntity();
    
    
    void tambahalamat(smmBusRelTable bus, str koloms, str kolomt, 
        str kolomu, str kolomv, str kolomw, str kolomx)
    {
        DirParty                        dirParty;
        DirPartyPostalAddressView       dirPartyPostalAddressView;
        DirPartyContactInfoView         dirPartyContactInfo;   
        ;
        
        /*menambahkan alamat dan telp*/
        dirparty = DirParty::constructFromCommon(summbus);
        //dirparty = DirParty::constructFromCommon(summbus);

        //fill address
        dirPartyPostalAddressView.LocationName      ='Alamat';
        dirPartyPostalAddressView.CountryRegionId   ='IDN';
        if(kolomv != "")
        {
            //kolom v
            dirPartyPostalAddressView.Street            = kolomv;
        }
        if(kolomw != "")
        {
            //kolom w
            dirPartyPostalAddressView.City              = kolomw;
        }                        
        if(kolomx != "")
        {
            //kolom x
            DirPartyPostalAddressView.ZipCode           = kolomx;
        }
        //Fill address
        dirParty.createOrUpdatePostalAddress(dirPartyPostalAddressView);
        
        //fill phone
        if(koloms != "")
        {
            dirPartyContactInfo.LocationName    ='Telp';
            //kolom s
            dirPartyContactInfo.Locator         = koloms;
            dirPartyContactInfo.Type            = LogisticsElectronicAddressMethodType::Phone;
            dirPartyContactInfo.IsPrimary       = NoYes::Yes;
            dirParty.createOrUpdateContactInfo(dirPartyContactInfo);
        }
        if(kolomt != "")
        {
            dirPartyContactInfo.LocationName    ='Ponsel';
            //kolom t
            dirPartyContactInfo.Locator         = kolomt;
            dirPartyContactInfo.Type            = LogisticsElectronicAddressMethodType::Phone;
            dirPartyContactInfo.IsPrimary       = NoYes::No;
            dirParty.createOrUpdateContactInfo(dirPartyContactInfo);
        }
        if(kolomu != "")
        {
            dirPartyContactInfo.LocationName    ='Email';
            //kolom u
            dirPartyContactInfo.Locator         = kolomu;
            dirPartyContactInfo.Type            = LogisticsElectronicAddressMethodType::Email;
            dirPartyContactInfo.IsPrimary       = NoYes::No;
            dirParty.createOrUpdateContactInfo(dirPartyContactInfo);
        }
        
    }
    
    ;

    tDate = date2str(systemDateGet(), 321, DateDay::Digits2, DateSeparator::Hyphen, DateMonth::Digits2, DateSeparator::Hyphen, DateYear::Digits4);
    tTime = time2Str(TimeNow(), TimeSeparator::Colon, TimeFormat::AMPM);

    //untuk membedakan organisasi atau bukan
    _isPerson = true;

    if (!WinAPI::fileExists(FileNamePath))
            throw error(strfmt("@SYS109820", FileNamePath));
    if (workbooks.open(FileNamePath, false /*Update links*/, true /*Read only*/))
    {
        workbook   = workbooks.item(1);
        workSheets = workbook.worksheets();
        workSheet  = workSheets.itemFromNum(1); //worksheet keberapa dari excel di mulai dari angka 1
        cells      = workSheet.cells();

        //di baca dari record ke ...
        i=4;
        while (true)
            {
                //filter di bawah agar uploadnya ga semua data, cukup 5 saja. tinggal hapus saja filter i nya
                if (cells.item(i, 1).value().bStr() == "" || i > 6)
                    break;
                else
                {
                    ttsbegin;
                    try{
                        caninsert = true;
                        _isPerson = true;
                        //kolom d
                        if (cells.item(i, 4).value().bStr() == "Y")
                        {
                            _isPerson = false;
                        }

                        if(_isPerson)
                        {
                            pers.initValue();
                            //dicky = cells.item(i, 9).value().bStr();
                            //temporary = str2int(cells.item(i, 9).value().bStr()); //0:none, 1:single, 2:married, 3:divorced, 4:widowed
                            //temporary = 1;

                            //do not change
                            pers.NameSequence = 5637144576;
                            pers.LanguageId = "en-us";

                            //kolom a
                            pers.PartyNumber = cells.item(i, 1).value().bStr();
                            //kolom b
                            pers.Name = cells.item(i, 2).value().bStr();
                            //kolom c
                            pers.NameAlias = cells.item(i, 3).value().bStr();
                            /*
                            no data required for kolom d-h
                            */
                            //kolom i
                            pers.MaritalStatus = str2int(cells.item(i, 9).value().bStr());
                            //kolom j
                            pers.Gender = str2int(cells.item(i, 10).value().bStr()); //1:male, 2:woman
                            //kolom k
                            pers.BirthMonth = str2int(cells.item(i, 11).value().bStr());
                            //kolom l
                            pers.BirthDay = str2int(cells.item(i, 12).value().bStr());
                            //kolom m
                            pers.BirthYear = str2int(cells.item(i, 13).value().bStr());
                            //kolom n
                            pers.AnniversaryMonth = str2int(cells.item(i, 14).value().bStr());
                            //kolom o
                            pers.AnniversaryDay = str2int(cells.item(i, 15).value().bStr());
                            //kolom p
                            pers.AnniversaryYear = str2int(cells.item(i, 16).value().bStr());
                            //kolom r
                            pers.Memo = cells.item(i, 18).value().bStr();
                            if(pers.validateWrite())
                            {
                                pers.insert();
                            }
                            else
                            {
                                caninsert = false;
                            }
                        }
                        else
                        {
                            party.initValue();
                            //do not change
                            party.LanguageId = "en-us";

                            //kolom a
                            party.PartyNumber = cells.item(i, 1).value().bStr();
                            //kolom b
                            party.Name = cells.item(i, 2).value().bStr();
                            //kolom c
                            party.NameAlias = cells.item(i, 3).value().bStr();
                            //kolom R
                            party.Memo = cells.item(i, 18).value().bStr();

                            if(party.validateWrite())
                            {
                                party.insert();
                            }
                            else
                            {
                                caninsert = false;
                            }
                        }

                        summbus.initValue();

                        //do not change
                        summbus.DirectMail = 1;
                        summbus.BusRelTypeId = "Prospect";
                        summbus.OpenToTime=57600;
                        summbus.OpenFromTime=28800;
                        summbus.Imported = 0;
                        summbus.Currency = "IDR";
                        summbus.CustGroup = "010";
                        summbus.OneTimeRelation = 0;
                        summbus.CreditMax = 0;
                        summbus.MandatoryCreditLimit = 0;
                        summbus.InclTax = 1;
                        summbus.Blocked = 0;
                        summbus.TaxGroup = "PPN10%";

                        if(_isPerson)
                        {
                            summbus.BusRelAccount = pers.PartyNumber;
                            summbus.Party = pers.RecId;
                        }
                        else
                        {
                            summbus.BusRelAccount = party.PartyNumber;
                            summbus.Party = party.RecId;
                        }

                        //kolom e
                        summbus.LineOfBusinessId=cells.item(i, 5).value().bStr();
                        //kolom f
                        summbus.SDX_SalesCodeId = cells.item(i, 6).value().bStr();
                        //kolom Q
                        summbus.SDX_CustChannelId  = cells.item(i, 17).value().bStr();
                        //kolom R
                        summbus.Memo = cells.item(i, 18).value().bStr();

                        if(summbus.validateWrite())
                        {
                            summbus.insert();
                        }
                        else
                        {
                            caninsert = false;
                        }

                        if(_isPerson)
                        {
                            person.Person = pers.RecId;
                            
                            person.initValue();
                            //kolom G
                            person.FirstName = cells.item(i, 7).value().bStr();
                            //kolom H
                            if(cells.item(i, 8).value().bStr() != "")
                            {
                                person.LastName = cells.item(i, 8).value().bStr();
                                person.MiddleName = "";
                            }
                            else                            
                            {
                                person.LastName = "";
                                person.MiddleName = "";
                            }
                            //person.ValidFrom = str2datetime(tDate + " " + tTime, 321);
                            person.ValidTo = str2datetime("2154/12/31 23:59:59", 321);
                            if(person.validateWrite())
                            {
                                person.insert();
                            }
                            else
                            {
                                caninsert = false;
                            }

                        }

                        tambahalamat(summbus,cells.item(i, 19).value().bStr(),
                            cells.item(i, 20).value().bStr(),
                            cells.item(i, 21).value().bStr(),
                            cells.item(i, 22).value().bStr(),
                            cells.item(i, 23).value().bStr(),
                            cells.item(i, 24).value().bStr());

                        /* kunci di sini untuk latihan*/
                        //caninsert = false;
                        

                        if(caninsert)
                        {
                            ttsCommit;
                            caninsert = true;
                        }
                        else
                        {
                            ttsAbort;
                            caninsert = false;
                        }
                        
                        //info(strFmt("%1", pers.RecId));
                    }
                    catch
                    {
                        info(strFmt("Gagal di baris %1", i));
                        ttsAbort;
                    }
                    i++;
                }
        }
    }
    info(strFmt("Beres upload sebanyak %1",i-3));
    workbooks.close();
}

get the tmplte prospect-20140513

  1. No comments yet.
  1. No trackbacks yet.

Please kindly Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: