Category: Ax 2012

Merging two queries ranges

HI Below sample code used to merge 2 queries ranges.

private void merge(Query _queryPRA, Query _this)
{
QueryBuildDataSource rpa, pfr;
int rpaRanges,i, rpaFilters;
QueryBuildRange range;
QueryFilter queryFilter;

//Fetch ranges from Query
rpaRanges =_queryPRA.dataSourceTable(tableNum(SalesTable)).rangeCount();
for (i=1 ; i<=rpaRanges; i++)
{
range = _queryPRA.dataSourceTable(tableNum(SalesTable)).range(i);

_this.dataSourceTable(tablenum(SalesTable)).addRange(_queryPRA.dataSourceTable(tableNum(SalesTable)).range(i).field()).value(queryValue(range.value()));
}

//fetch user filters from Dialog
rpaFilters = _queryPRA.queryFilterCount();

for (i=1 ; i<=rpaFilters; i++)
{
queryFilter = _queryPRA.queryFilter(i);

_this.dataSourceTable(tablenum(SalesTable)).addRange(fieldName2id(tableNum(SalesTable), _queryPRA.queryFilter(i).field())).value(queryValue(queryFilter.value()));
}
}

Advertisements

SQL in X++, range in where clause

Below case is when ‘AccessFeedsFilter’ has value it should fetch that reocrds only if its empty: it should fetch all

 

while select SKUNumber, ItemId, inventDimId,LifeCycleStatusFlag,ItemName, InboundDepth, InboundHeight, InboundWeight, InboundWidth,
OutboundDepth, OutboundHeight, OutboundWeight, OutboundWidth, ActualDepth, ActualHeight, ActualWeight, ActualWidth, RetailVariantId, Description, BasePrice
from productsAndVariants
where (productsAndVariants.LifeCycleStatusFlag == ogoParameters.AccessFeedsFilter && ogoParameters.AccessFeedsFilter != “”) || (ogoParameters.AccessFeedsFilter == “” && true)

Split text multiple lines in Email body

HI, Below smaple code used to show splited text in email body.

{

SysMailer mailer;
SysEmailParameters parameters;
InteropPermission permission = new InteropPermission(InteropKind::ComInterop);
OWIStoreTable OWIStoreTable;
Notes bodymsg, bodymsghtml;
ListIterator iterator;
List strlist=new List(Types::String);
;
permission.assert();
mailer = new SysMailer();
parameters = SysEmailParameters::find();

if (parameters.SMTPRelayServerName)
{
mailer.SMTPRelayServer(parameters.SMTPRelayServerName,
parameters.SMTPPortNumber,
parameters.SMTPUserName,
SysEmailParameters::password(),
parameters.NTLM);
}
else
{
mailer.SMTPRelayServer(parameters.SMTPServerIPAddress,
parameters.SMTPPortNumber,
parameters.SMTPUserName,
SysEmailParameters::password(),
parameters.NTLM);
}

mailer.fromAddress(‘test@AX.com’);
mailer.tos().appendAddress(‘AAA@gmail.com’);
mailer.subject(‘System Test’);

strlist =strSplit(‘AAA  BBB’,”\n”);

iterator = new ListIterator(strlist);

while(iterator.more())
{
bodymsg += strFmt(“%1<br>”,iterator.value());
iterator.next();
}

bodymsg = strReplace(bodymsg,’\n’,'<br>’);
bodymsg =bodymsg + “\n” + strFmt(“Last order %2 received for this webtore on: %1”, _lastSalesOrder.createdDateTime);

bodymsghtml= strFmtLB(bodymsg);

mailer.htmlBody(bodymsghtml);

mailer.sendMail();
}

Email sending using file template

HI, below sample code used to send email using Email tempalte formate in multiple lines and header formate.

static void sendMail(Email _defEmail, SysEmailId _emailTemplate, PurchTable PurchTable)
{
SysEmailParameters parameters = SysEmailParameters::find();
SMTPRelayServerName relayServer;
SMTPPortNumber portNumber;
SMTPUserName userName;
SMTPPassword password;
Str1260 subject;
Notes msgbody ;
InteropPermission interopPermission;
SysMailer mailer;
System.Exception e;

SysEmailTable table;
SysEmailMessageTable message;

str newMessageBody;

;

if(_defEmail)
{
if (parameters.SMTPRelayServerName)
relayServer = parameters.SMTPRelayServerName;
else
relayServer = parameters.SMTPServerIPAddress;

portNumber = parameters.SMTPPortNumber;
userName = parameters.SMTPUserName;
password = SysEmailParameters::password();

table = SysEmailTable::find(_emailTemplate);
message = SysEmailMessageTable::find(table.EmailId, table.DefaultLanguage);
if (message)
{
newMessageBody = VendorSendEmail::renderLetter(message,PurchTable);
}
subject = message.Subject;
msgbody = newMessageBody;

CodeAccessPermission::revertAssert();

try
{
interopPermission = new InteropPermission(InteropKind::ComInterop);
interopPermission.assert();
mailer = new SysMailer();
mailer.SMTPRelayServer(relayServer,portNumber,userName,password, parameters.NTLM);
mailer.fromAddress(SysUserInfo::find().Email);
mailer.tos().appendAddress(_defEmail);
mailer.subject(subject);
mailer.htmlBody(msgbody);
mailer.sendMail();
CodeAccessPermission::revertAssert();
info(“@CIT957”);
}
catch (Exception::CLRError)
{
e = ClrInterop::getLastException();

while (e)
{
info(e.get_Message());
e = e.get_InnerException();
}
CodeAccessPermission::revertAssert();
info (“error”);
}
}
else
error(“done”);

}

Reading text file data from folder

HI, Below sample code used to read multiple files

  1. Read data from file,Write to new file in different folder
  2. Delete files

Read and write file:

public void writeToHistoryFile(FilePath _fromFilePath)//Source filepath
{
#File
System.IO.StreamWriter streamWriterHistory;
Counter countFiles;
FileName fileNameHistory;
str inputLineHistory;
Set permissionSetHistory;
str directoryHistory, filePathHistory;
container fileListConHistory, record;
CommaTextIo commaTextIoHistory;

directoryHistory = _fromFilePath;
if(directoryHistory == “”)
{
return;
}
fileListConHistory = this.findMatchingFiles(directoryHistory);
//permissionSetHistory.add(new InteropPermission(InteropKind::ClrInterop));
for (countFiles=1; countFiles <= conlen(fileListConHistory); countFiles++)
{
ttsBegin;
inputLineHistory = “”;
fileNameHistory = “Newfolderpath”;

fileNameHistory += “\\newfilename”+”_”+Winapi::createGUID()+”_”;
fileNameHistory += date2Str(today(), 213, 2, DateSeparator::None, 2, DateSeparator::None, 4);
fileNameHistory +=” “;
fileNameHistory += time2str(timeNow(),TimeSeparator::Dot, TimeFormat::AMPM);
fileNameHistory += “.txt”;
streamWriterHistory= new System.IO.StreamWriter(fileNameHistory,
false,
System.Text.Encoding::GetEncoding(28591));

permissionSetHistory = new Set(Types::Class);

permissionSetHistory.add(new InteropPermission(InteropKind::ClrInterop));
filePathHistory = conPeek(fileListConHistory,countFiles);

permissionSetHistory.add(new FileIOPermission(filePathHistory, ‘r’));
permissionSetHistory.add(new FileIOPermission(filePathHistory, ‘rw’));
CodeAccessPermission::assertMultiple(permissionSetHistory);

commaTextIoHistory = new CommaTextIo(filePathHistory,#io_read);
if (! commaTextIoHistory || commaTextIoHistory.status() != IO_Status::Ok)
{
throw error(“@SYS19358”);
}
while (commaTextIoHistory.status() == IO_Status::Ok)
{
record = commaTextIoHistory.read();

if(!record)
{
CodeAccessPermission::revertAssert();
break;
}

//record = conIns(record, conLen(record) +1);
if(!inputLineHistory)
inputLineHistory = con2Str(record);
else
inputLineHistory = “\n” + con2Str(record);

streamWriterHistory.WriteLine(inputLineHistory,true);
}
CodeAccessPermission::revertAssert();

// }
streamWriterHistory.Close();
commaTextIoHistory.finalize();
ttsCommit;
}
}

Delete file:

static boolean importFromWorkstation(FilePath _deleteFile = “”)
{
//MPSWorkstation _tWorkstation
int iNumberOfFiles;
FileName sFileName,fileNamePerm, inFile;
InteropPermission interopPerm;
Set permissionSet, permSetFiles;
System.Array files;
int i, j;
container fList;
#File
;

permissionSet = new Set(Types::Class);
permissionSet.add(new InterOpPermission(InteropKind::ClrInterop));

CodeAccessPermission::assertMultiple(permissionSet);

files = System.IO.Directory::GetFiles(_deleteFile);
for( i=0; i<ClrInterop::getAnyTypeForObject(files.get_Length()); i++ )
{
fList = conins(fList, conlen(fList)+1, ClrInterop::getAnyTypeForObject(files.GetValue(i)));
}

for(j=1;j<=conlen(fList); j++)

{
sFileName = conpeek(fList, j);
inFile = sFileName;
ttsbegin;

System.IO.File::Delete(inFile);

ttscommit;
}
CodeAccessPermission::revertAssert();

return true;
}

Wokflow approver name and date time

HI, Below code used to get workflow approve name based on purchase order Id or RecId.

select firstonly workflowTrackingStatusTable
order by RecId desc
where workflowTrackingStatusTable.ContextTableId == tableNum(PurchTable)
&& workflowTrackingStatusTable.ContextRecId == purchTable.RecId
&& workflowTrackingStatusTable.TrackingStatus == WorkflowTrackingStatus::Completed;
select * from workflowTrackingTable
order by CreatedDateTime desc, RecId desc
where workflowTrackingTable.WorkflowTrackingStatusTable == workflowTrackingStatusTable.RecId
&& workflowTrackingTable.TrackingContext == WorkflowTrackingContext::WorkItem
&& workflowTrackingTable.TrackingType == WorkflowTrackingType::Approval;

select firstOnly dirPersonUser
order by ValidTo desc
where dirPersonUser.User == workflowTrackingTable.User;

select dirPerson
where dirPerson.RecId == dirPersonUser.PersonParty
join hcmWorker
where hcmWorker.Person == dirPerson.RecId;

select hcmPositionWorkerAssignment
order by ValidTo desc
where hcmPositionWorkerAssignment.Worker == hcmWorker.RecId
join hcmPosition
where hcmPosition.RecId == hcmPositionWorkerAssignment.Position;

name = dirPerson.Name;

select * from workflowTrackingCommentTable where workflowTrackingCommentTable.WorkflowTrackingTable == workflowTrackingTable.RecId;

approvedBy = strFmt(“@SYS3492” + ‘ < %1, %2 – %3 >’, name,
hcmPosition.description(),
DateTimeUtil::applyTimeZoneOffset(workflowTrackingCommentTable.createdDateTime, DateTimeUtil::getCompanyTimeZone()));

Build query range on Multiple Tables

str qvalue;

qvalue = strFmt(‘((%1 like “%5”) || (%2 like “%5”) || (%3 like “%5”) || (%4 like “%5”) || (%6 like “%5”) || (%7 like “%5”) || (%8 like “%5”))’,
fieldStr(ProductsAndVariants, ItemId),
fieldStr(ProductsAndVariants, ItemName),
fieldStr(ProductsAndVariants, VariantDescription),
fieldStr(ProductsAndVariants, ItemSearchName),
_searchText,
fieldStr(CustVendExternalItem, ExternalItemId ),
fieldStr(CustVendExternalItem, ExternalItemTxt),
fieldStr(InventItemBarcode, itemBarCode));

 

qbs = query.addDataSource(tableNum(ProductAndVariantSearch));
qbr = qbs.addRange(fieldNum(ProductAndVariantSearch, DataAreaId));
qbr.value(qvalue);