Axapta: customer excel upload

Customer Excel upload

class Ale_CustomerMastersExcelUpload
{
SysExcelApplication application;
SysExcelWorkbooks workbooks;
SysExcelWorkbook workbook;
SysExcelWorksheets worksheets;
SysExcelWorksheet worksheet;
SysExcelCells cells;
}

///---------------------------------------------------------------------------------------------------------------

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 "";
}

////----------------------------------------------------------------------------------------------------

private void contactPersonContactDetail(DirPartyRecId _partyRecId,
LogisticsElectronicAddressLocator _email,
LogisticsElectronicAddressLocator _website,
LogisticsElectronicAddressLocator _Phone,
LogisticsElectronicAddressLocator _telex,
LogisticsElectronicAddressLocator _fax)
{
DirParty DirParty;
DirPartyContactInfoView contactView;

DirParty = DirParty::constructFromPartyRecId(_partyRecId);
contactView.clear();

if(_Phone)
{
contactView.LocationName = "Phone Number";
contactView.Locator = _Phone;
contactView.Type = LogisticsElectronicAddressMethodType::Phone;
contactView.Party = _partyRecId;
contactView.IsPrimary = NoYes::Yes;
dirParty.createOrUpdateContactInfo(contactView);
}

if(_fax)
{
contactView.LocationName = "Fax Number";
contactView.Locator = _fax;
contactView.Type = LogisticsElectronicAddressMethodType::Fax;
contactView.Party = _partyRecId;
contactView.IsPrimary = NoYes::Yes;
dirParty.createOrUpdateContactInfo(contactView);
}

if(_website)
{
contactView.LocationName = "Website";
contactView.Locator = _website;
contactView.Type = LogisticsElectronicAddressMethodType::URL;
contactView.Party = _partyRecId;
contactView.IsPrimary = NoYes::Yes;
dirParty.createOrUpdateContactInfo(contactView);
}

if(_email)
{
contactView.LocationName = "Email";
contactView.Locator = _email;
contactView.Type = LogisticsElectronicAddressMethodType::Email;
contactView.Party = _partyRecId;
contactView.IsPrimary = NoYes::Yes;
dirParty.createOrUpdateContactInfo(contactView);
}
if(_telex)
{
contactView.LocationName = "Email";
contactView.Locator = _telex;
contactView.Type = LogisticsElectronicAddressMethodType::Email;
contactView.Party = _partyRecId;
contactView.IsPrimary = NoYes::Yes;
dirParty.createOrUpdateContactInfo(contactView);
}
}

/////---------------------------------------------------------------------------------------------

public DimensionDefault createDefaultDimension(container _attr, container _value, boolean _createIfNotFound = true)
{
DimensionAttributeValueSetStorage valueSetStorage = new DimensionAttributeValueSetStorage();
DimensionDefault result;
int i;
DimensionAttribute dimensionAttribute;
DimensionAttributeValue dimensionAttributeValue;
//_attr is dimension name in table DimensionAttribute
container conAttr = _attr;
container conValue = _value;
str dimValue;

for (i = 1; i <= conLen(conAttr); i++)
{
dimensionAttribute = dimensionAttribute::findByName(conPeek(conAttr,i));

if (dimensionAttribute.RecId == 0)
{
continue;
}

dimValue = conPeek(conValue,i);

if (dimValue != "")
{
// _createIfNotFound is "true". A dimensionAttributeValue record will be created if not found.
dimensionAttributeValue = dimensionAttributeValue::findByDimensionAttributeAndValue(dimensionAttribute,dimValue,false,_createIfNotFound);

// Add the dimensionAttibuteValue to the default dimension
valueSetStorage.addItem(dimensionAttributeValue);
}
}
result = valueSetStorage.save();
return result;
}
////---------------------------------------------------------------------------------------------------------

public void importCustBankAccount()
{
custBankAccount custBankAccount;
LogisticsLocation LogisticsLocation;
LogisticsPostalAddress logisticsPostalAddress;
Dialog dialog;
DialogGroup dialogGroup;
DialogField dialogfilename;
Filename filename;
COMVariantType type;
int row;

dialog = new Dialog("Select File For customer Bank Master Import");
dialogGroup = dialog.addGroup("Excel File");
dialogfilename = dialog.addField(extendedTypeStr(FilenameOpen));

if (dialog.run())
{
application = SysExcelApplication::construct();
workbooks = application.workbooks();
filename=dialogfilename.value();
try
{
workbooks.open(filename);
}
catch (Exception::Error)
{
throw error(strFmt("@GLS101746",filename));
}
workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
row = 3;

ttsBegin;
do
{
row++;
logisticsLocation.Description = this.COMVariant2Str(cells.item(row, 9).value());
logisticsLocation.insert();

logisticsPostalAddress.Street = this.COMVariant2Str(cells.item(row, 9).value());
logisticsPostalAddress.Address = this.COMVariant2Str(cells.item(row, 9).value());
logisticsPostalAddress.Location = LogisticsLocation.RecId;
logisticsPostalAddress.insert();

custBankAccount.initValue();
custBankAccount.custAccount = this.COMVariant2Str(cells.item(row, 1).value());
custBankAccount.AccountID = this.COMVariant2Str(cells.item(row, 2).value());
custBankAccount.AccountNum = this.COMVariant2Str(cells.item(row, 3).value());
custBankAccount.Name = this.COMVariant2Str(cells.item(row, 4).value());
custBankAccount.RegistrationNum = this.COMVariant2Str(cells.item(row, 5).value());
custBankAccount.SWIFTNo = this.COMVariant2Str(cells.item(row, 6).value());
custBankAccount.BankIBAN = this.COMVariant2Str(cells.item(row, 7).value());
custBankAccount.Location = LogisticsLocation.RecId;
custBankAccount.insert();

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

ttscommit;
info(strFmt("%1 records successfully inserted", row - 3));
application.quit();
}
}
///--------------------------------------------------------------------------------------------------------
public void importCustomerMaster()
{
Dialog dialog;
DialogGroup dialogGroup;
DialogField dialogfilename;
Filename filename;

COMVariantType type;
int row;
NoYesCombo NoYes;
NoYes NoYes1;
CustVendorBlocked CustVendorBlocked;

custTable custTable;
CustTable custTablePrimaryContact;
DirPartyTable DirPartyTable;
DirPartyTable dirPartyTableParent;
LogisticsPostalAddress address;
DirPartyPostalAddressView addressView;
DirParty dirParty;
DirPartyRecId partyRecId;
ContactPerson contactPerson;
DirPerson dirPerson, dirPersonEmployee;
DirPartyLocation dirPartylocation;
int arrindex;
Struct struct = new Struct();
container defaultDimension;
container attrib , value ;
LogisticsElectronicAddress logisticsElectronicAddress;

;
dialog = new Dialog("Select File For customer Master Import");
dialogGroup = dialog.addGroup("Excel File");
dialogfilename = dialog.addField(extendedTypeStr(FilenameOpen));

if (dialog.run())
{
application = SysExcelApplication::construct();
workbooks = application.workbooks();
filename=dialogfilename.value();
try
{
workbooks.open(filename);
}
catch (Exception::Error)
{
throw error(strFmt("@GLS101746",filename));
}
workbook = workbooks.item(1);
worksheets = workbook.worksheets();
worksheet = worksheets.itemFromNum(1);
cells = worksheet.cells();
row = 3;
ttsBegin;
do
{
row++;
custTable.initValue();
custTable.AccountNum = this.COMVariant2Str(cells.item(row, 1).value());
custTable.InvoiceAccount = this.COMVariant2Str(cells.item(row, 2).value());
//custTable.FamilyGroup = this.COMVariant2Str(cells.item(row, 3).value());
custTable.Currency = this.COMVariant2Str(cells.item(row, 5).value());
custTable.custGroup = this.COMVariant2Str(cells.item(row, 6).value());
custTable.CustClassificationId = this.COMVariant2Str(cells.item(row, 7).value());
//DUNS number 8
//custTable.WorldCheckDone = str2enum(NoYes, cells.item(row, 9).value().bStr());
//custTable.WorldCheckDate = cells.item(row, 10).value().date();

select firstFast Name from dirPersonEmployee
where dirPersonEmployee.Name == this.COMVariant2Str(cells.item(row, 11).value());
custTable.MainContactWorker = HcmWorker::findByPerson(dirPersonEmployee.RecId).RecId;
custTable.SegmentId = this.COMVariant2Str(cells.item(row, 12).value());
custTable.SubsegmentId = this.COMVariant2Str(cells.item(row, 13).value());
custTable.DlvTerm = this.COMVariant2Str(cells.item(row, 14).value());
custTable.Blocked = str2enum(CustVendorBlocked, cells.item(row, 15).value().bStr());
custTable.PaymTermId = this.COMVariant2Str(cells.item(row, 16).value());
custTable.PaymMode = this.COMVariant2Str(cells.item(row, 17).value());
custTable.DlvMode = this.COMVariant2Str(cells.item(row, 18).value());
//our account number: not require :19
custTable.CreditRating = this.COMVariant2Str(cells.item(row, 20).value());
custTable.CreditMax = cells.item(row, 21).value().double();
custTable.InventSiteId = this.COMVariant2Str(cells.item(row, 28).value());
custTable.InventLocation = this.COMVariant2Str(cells.item(row, 29).value());
custTable.LineOfBusinessId = this.COMVariant2Str(cells.item(row, 36).value());
//custTable.IsParent = str2enum(NoYes1, cells.item(row, 37).value().bStr());
//stTable.RegExpDate = cells.item(row, 42).value().date();

select firstFast recId from dirPartyTableParent
where dirPartyTableParent.Name == this.COMVariant2Str(cells.item(row, 38).value());
//custTable.ParentAccount = CustTable::findByPartyRecId(dirPartyTableParent.RecId).AccountNum;//change to code instead of name
//custTable.ParentAccount = this.COMVariant2Str(cells.item(row, 38).value());

if(this.COMVariant2Str(cells.item(row, 55).value()) != "")
{
attrib += ["BusinessUnit"];
value += [this.COMVariant2Str(cells.item(row, 55).value())];
}
if(this.COMVariant2Str(cells.item(row, 59).value()) != "")
{
attrib += ["CVBPM"];
value += [this.COMVariant2Str(cells.item(row, 59).value())];
}
if(this.COMVariant2Str(cells.item(row, 56).value()) != "")
{
attrib += ["Department"];
value += [this.COMVariant2Str(cells.item(row, 56).value())];
}
if(this.COMVariant2Str(cells.item(row, 58).value()) != "")
{
attrib += ["Project"];
value += [this.COMVariant2Str(cells.item(row, 58).value())];
}
if(this.COMVariant2Str(cells.item(row, 57).value()) != "")
{
attrib += ["REGION"];
value += [this.COMVariant2Str(cells.item(row, 57).value())];
}
if(this.COMVariant2Str(cells.item(row, 60).value()) != "")
{
attrib += ["Worker"];
value += [this.COMVariant2Str(cells.item(row, 60).value())];
}
custTable.DefaultDimension = this.createDefaultDimension(attrib,value);
/*AxdDimensionUtil::getDimensionAttributeValueSetId([arrindex,
"BusinessUnit",this.COMVariant2Str(cells.item(row, 55).value()),
"CVBPM", this.COMVariant2Str(cells.item(row, 59).value()) ,
"Department", this.COMVariant2Str(cells.item(row, 56).value()),
"Project", this.COMVariant2Str(cells.item(row, 58).value()),
"REGION", this.COMVariant2Str(cells.item(row, 57).value()),
"Worker", this.COMVariant2Str(cells.item(row, 60).value())]);*/
custTable.insert();
partyRecId = custTable.Party;

//customer name
select forUpdate DirPartyTable where DirPartyTable.RecId == custTable.Party;
DirPartyTable.Name = this.COMVariant2Str(cells.item(row, 4).value());
DirPartyTable.NameAlias = this.COMVariant2Str(cells.item(row, 4).value());
DirPartyTable.update();

//Address details
address.clear();
address.Street = this.COMVariant2Str(cells.item(row, 24).value());
address.City = this.COMVariant2Str(cells.item(row, 25).value());
address.State = this.COMVariant2Str(cells.item(row, 26).value());
address.CountryRegionId = this.COMVariant2Str(cells.item(row, 27).value());
address.PostBox = this.COMVariant2Str(cells.item(row, 22).value()); //

addressView.LocationName = this.COMVariant2Str(cells.item(row, 23).value());
addressView.IsPrimary = NoYes::Yes;
addressView.Party = custTable.Party;
addressview.initFromPostalAddress(address);

DirParty = DirParty::constructFromPartyRecId(addressView.Party );
DirParty.createOrUpdatePostalAddress(addressView);
/*
//Tax registration details
select RecId from dirPartylocation
where //dirPartylocation.Location == addressView.Location &&
dirPartylocation.Party == addressView.Party;
//Year established
if(cells.item(row, 33).value().date() && this.COMVariant2Str(cells.item(row, 35).value()))
{
this.locationTaxRegistrationUpdate(dirPartylocation.RecId,
"YearofEst",
cells.item(row, 33).value().date(),
dateMax() ,
"",
this.COMVariant2Str(cells.item(row, 35).value()));
}

//Trade lic num
if(cells.item(row, 40).value().date() && this.COMVariant2Str(cells.item(row, 39).value()))
{
this.locationTaxRegistrationUpdate(dirPartylocation.RecId,
"TradeLicense",
today() ,
cells.item(row, 40).value().date(),
"",
this.COMVariant2Str(cells.item(row, 39).value()));
}

//Tax reg no
if(cells.item(row, 42).value().date() && this.COMVariant2Str(cells.item(row, 41).value()))
{
this.locationTaxRegistrationUpdate(dirPartylocation.RecId,
"TaxRegDet",
today() ,
cells.item(row, 42).value().date(),
"",
this.COMVariant2Str(cells.item(row, 41).value()));
}
*/

//Customer contact detail
this.contactPersonContactDetail(custTable.Party,
this.COMVariant2Str(cells.item(row, 32).value()),
this.COMVariant2Str(cells.item(row, 34).value()),
this.COMVariant2Str(cells.item(row, 30).value()),
"",
this.COMVariant2Str(cells.item(row, 31).value()));

// ContactPerson details
// 1st contact
if(this.COMVariant2Str(cells.item(row, 43).value()))
{
DirPerson.clear();
DirPerson.Name = this.COMVariant2Str(cells.item(row, 43).value()) + " " +
this.COMVariant2Str(cells.item(row, 44).value()) + " " +
this.COMVariant2Str(cells.item(row, 45).value());
DirPerson.NameAlias = DirPerson.Name;
DirPerson.NameSequence = DirNameSequence::find("FirstLastMiddle").RecId;
DirPerson.insert();

DirPersonName::addNewNameToPerson(DirPerson.RecId,
this.COMVariant2Str(cells.item(row, 43).value()),
this.COMVariant2Str(cells.item(row, 44).value()),
this.COMVariant2Str(cells.item(row, 45).value()));

contactPerson.clear();
contactPerson.initFromcustTable(custTable);
contactPerson.Party = dirPerson.RecId;
contactPerson.insert();

this.contactPersonContactDetail(contactPerson.Party,
this.COMVariant2Str(cells.item(row, 46).value()),
"",
this.COMVariant2Str(cells.item(row, 47).value()),
this.COMVariant2Str(cells.item(row, 48).value()),
"");
//primary contact update
select forUpdate custTablePrimaryContact where custTablePrimaryContact.RecId == CustTable.RecId;
custTablePrimaryContact.ContactPersonId = contactPerson.ContactPersonId;
custTablePrimaryContact.update();
}

//2nd contact
if(this.COMVariant2Str(cells.item(row, 49).value()))
{
DirPerson.clear();
DirPerson.Name = this.COMVariant2Str(cells.item(row, 49).value()) + " " +
this.COMVariant2Str(cells.item(row, 50).value()) + " " +
this.COMVariant2Str(cells.item(row, 51).value());
DirPerson.NameAlias = DirPerson.Name;
DirPerson.NameSequence = DirNameSequence::find("FirstLastMiddle").RecId;
DirPerson.insert();

DirPersonName::addNewNameToPerson(DirPerson.RecId,
this.COMVariant2Str(cells.item(row, 49).value()),
this.COMVariant2Str(cells.item(row, 50).value()),
this.COMVariant2Str(cells.item(row, 51).value()));

contactPerson.clear();
contactPerson.initFromcustTable(custTable);
contactPerson.Party = dirPerson.RecId;
contactPerson.insert();

this.contactPersonContactDetail(contactPerson.Party,
this.COMVariant2Str(cells.item(row, 52).value()),
"",
this.COMVariant2Str(cells.item(row, 53).value()),
this.COMVariant2Str(cells.item(row, 54).value()),
"");
}

type = cells.item(row+1, 1).value().variantType();
}
while (type != COMVariantType::VT_EMPTY);
ttsCommit;
workbooks.close();
info(strFmt("%1 records successfully inserted", row - 3));
application.quit();
}
}
///-------------------------------------------------------------------------------------------------------
public void locationTaxRegistrationUpdate(RefRecId _dirPartyLocation,
TaxRegistrationTypeName _type,
TransDate _validFromDate = today(),
TransDate _validToDate = dateMax(),
Description _Description = "-",
TaxRegistrationNumber _TaxRegistrationNumber = "-")
{
TaxRegistration taxRegistration;
TaxRegistrationType taxRegType;

taxRegistration.initValue();
taxRegistration.DirPartyLocation = _dirPartyLocation;
taxRegistration.TaxRegistrationTypeApplicabilityRule = TaxRegistrationType::findByType(_type).RecId;
taxRegistration.description = _Description;
taxRegistration.RegistrationNumber = _TaxRegistrationNumber;
taxRegistration.ValidFrom = _validFromDate;
taxRegistration.validTo = _validToDate;

taxRegistration.insert();
}

Axapta

Friday 16 June 2017

customer excel upload

No comments:

Post a Comment