Author: Ahmed Muhammad Alam

AX Technical Consultant

Connecting other DB through X++ code in AX 2012

// X++, Main method in a class.

static public void Main(Args _args)

{

LoginProperty                       loginProperty;

OdbcConnection                      odbcConnection;

Statement                           statement;

ResultSet                           resultSet;

str                                 sql, criteria;

SqlStatementExecutePermission       perm;

;

 

// Set the information on the ODBC.

loginProperty = new LoginProperty();

loginProperty.setDSN(“RMSHQ”);

loginProperty.setDatabase(“CBHQ”);

 

//Create a connection to external database.

odbcConnection = new OdbcConnection(loginProperty);

 

if (odbcConnection)

{

/* sql = “SELECT * FROM MYTABLE WHERE FIELD = “

            + criteria

           + ” ORDER BY FIELD1, FIELD2 ASC ;”;*/

 

 

// sql = “SELECT * FROM V_TotalSales”;

// sql = “SELECT VTS.BatchNumber, VTS.ID, VTS.[Date] FROM [CBHQ].[dbo].[V_TotalSales] VTS GROUP BY   VTS.BatchNumber, VTS.ID, VTS.DATE”;

criteria = strFmt(” Where VTS.ID = ‘%1’ and VTS.[Date] = ‘%2’ “, ‘3002’, date2str(mkDate(28,04,2016), 213, DateDay::Auto, DateSeparator::Slash, DateMonth::Digits2, DateSeparator::Slash, DateDay::Auto));

 

 

sql = “SELECT VTS.ID, VTS.BatchNumber, VTS.[Date] FROM [CBHQ].[dbo].[V_TotalSales] VTS ”

+ criteria

+ ” GROUP BY   VTS.BatchNumber, VTS.ID, VTS.[DATE]”;

 

//Assert permission for executing the sql string.

perm = new SqlStatementExecutePermission(sql);

perm.assert();

 

//Prepare the sql statement.

statement = odbcConnection.createStatement();

resultSet = statement.executeQuery(sql);

 

//Cause the sql statement to run,

//then loop through each row in the result.

while (resultSet.next())

{

//It is not possible to get field 3 and then 1.

//Always get fields in numerical order, such as 1 then 2 the 3 etc.

//COLUMN SEQUENCE

// STOREID, STORE NAME, BATCHNUMBER, DATE, ITEMID, QTY, LINEAMOUNT, DISCOUNT AMOUNT, SALES PRICE

//info(strFmt(“%1, %2, %3, %4, %5, %6, %7, %8, %9 “, resultSet.getString(1), resultSet.getString(2), resultSet.getString(3), resultSet.getString(4), resultSet.getString(5), resultSet.getString(6), resultSet.getString(7), resultSet.getString(8),resultSet.getString(9)));

info(strFmt(“Batch Number %1,     Store ID : %2,     Trans Date:%3”, resultSet.getString(1), resultSet.getString(2), resultSet.getString(3)));

 

}

 

//Close the connection.

resultSet.close();

statement.close();

}

else

{

error(“Failed to log on to the database through ODBC.”);

}

}

 

SSRS reporting with Controller Class in AX 2012 R2 CU7

Follow the underneath steps in order to create the SSRS based report by using controller class.

STEP I : Create a temp table within the AX Environment.

STEP II: Create a query with above mentioned table.

STEP III: Contract Class.

class EmplDetailContract
{
RecId _hcmWorkerId;

}

*************************************************************

[
DataMemberAttribute(“Employee ID”)
]
public Recid parmHcmWorkerId(RecId hcmworkerid = _hcmWorkerId)
{
_hcmWorkerId = hcmWorkerId;
return _hcmWorkerId;
}

 

 

STEP IV: Controller Class.

class EmplDetailController eXTENDS SrsReportRunController
{
HcmWorker _hcmWorker;
}

*************************************************************

protected void preRunModifyContract()
{
// super();
str reportnameLocal;
EmplDetailContract contract;

contract = this.parmReportContract().parmRdpContract() as EmplDetailContract;

if(this.parmArgs().menuItemName() == menuitemOutputStr(EmplDetailReport))
{
_hcmWorker = args.record();
contract.parmHcmWorkerId(_hcmWorker.RecId);
reportnameLocal = ssrsReportStr(EmplDetailReport, Report);
}
}

*************************************************************

public static void main(Args _args)
{
EmplDetailController controller = new EmplDetailController();
TmpEmployeeDetail _emplDetailTable;

_emplDetailTable = _args.record() as TmpEmployeeDetail;

controller.parmArgs(_args);
controller.parmReportName(ssrsReportStr(EmplDetailReport, Report));
controller.parmShowDialog(false);
controller.startOperation();
}

 

STEP V: Data Provider Class

[
SRSReportParameterAttribute(classStr(EmplDetailContract))
]
class EmplDetailDP extends SRSReportDataProviderBase
{
TmpEmployeeDetail _employeeDetailTable;
}

*************************************************************

[
SRSReportDataSetAttribute(tableStr(TmpEmployeeDetail))
]
public TmpEmployeeDetail getData()
{
select * from _employeeDetailTable;
return _employeeDetailTable;
}

*************************************************************

private void insertintoEmplDetailTbl(HcmWorker _hcmworker)
{
_employeeDetailTable.clear();
_employeeDetailTable.BankCode = _hcmworker.BankCode;
_employeeDetailTable.BankIBAN = _hcmworker.BankIBAN;
_employeeDetailTable.ContractEndDate = _hcmworker.ContractEndDate;
_employeeDetailTable.ContractStartDate = _hcmworker.ContractStartDate;
_employeeDetailTable.EmplIdentNumber= _hcmworker.EmplIdentNumber;
_employeeDetailTable.EmployeeArabicName = _hcmworker.EmployeeArabicName;
_employeeDetailTable.EmployeeLeaveGroup = _hcmworker.EmployeeLeaveGroup;
_employeeDetailTable.Nationality = _hcmworker.Nationality;
_employeeDetailTable.insert();
}

*************************************************************

[
SysEntryPointAttribute(false)
]
public void processReport()
{
RecId _hcmworkerid;
HcmWorker _hcmworker;
EmplDetailContract contact = this.parmDataContract();

_hcmworker = HcmWorker::find(contact.parmHcmWorkerId());

this.insertintoEmplDetailTbl(_hcmworker);

}

 

STEP V: Create a AX Model project within VS and include a report. Remember to export the project and include report with in the Application Object Tree (AOT).

STEP VI: Create a “Output” menu item and link it with the controller class.

STEP VII: Add the newly created menu within the respective Form.

Using Google Map in AX 2012 CU7 R2

In class declaration

#Define.GoogleMapUrl(‘http://maps.google.com?q=\%1’)

*******************************************************************************

Form Init method

url = “Harmain Street” +”,”
+ “Jiddah” +”,”
+ “SA-02″ +”,”
+ “6101” +”,”
+ “SAU”;
url = strReplace(url, “\n”, “,”); // replace newline with commas
url = strfmt(#BingMapURL, url);

super();
ActiveX.Navigate(URL);
ActiveX.allowEdit(false);

*******************************************************************************

void clicked()
{
FormRun formRun;
Args args = new Args();

// #Define.MapURL(‘http://www.bing.com/maps/?q=\%1’)
;
url = Address.Street +”,”
+ Address.City +”,”
+ Address.State +”,”
+ Address.ZipCode +”,”
+ Address.CountryRegionId;
url = strReplace(url, “\n”, “,”); // replace newline with commas

url = System.Web.HttpUtility::UrlEncode(url);

args.name(formstr(MapViewer));
args.parm(strfmt(#BingMapURL, url));
formRun = classFactory.formRunClass(args);

formRun.init();
formRun.run();
formRun.wait();
}

Using Bing Map in AX 2012 CU7 R2

In class declaration

#Define.BingMapURL(‘http://www.bing.com/maps/?q=\%1’)

*******************************************************************************

Form Init method

url = “Harmain Street” +”,”
+ “Jiddah” +”,”
+ “SA-02″ +”,”
+ “6101” +”,”
+ “SAU”;
url = strReplace(url, “\n”, “,”); // replace newline with commas
url = strfmt(#BingMapURL, url);

super();
ActiveX.Navigate(URL);
ActiveX.allowEdit(false);

*******************************************************************************

void clicked()
{
FormRun formRun;
Args args = new Args();

// #Define.MapURL(‘http://www.bing.com/maps/?q=\%1’)
;
url = Address.Street +”,”
+ Address.City +”,”
+ Address.State +”,”
+ Address.ZipCode +”,”
+ Address.CountryRegionId;
url = strReplace(url, “\n”, “,”); // replace newline with commas

url = System.Web.HttpUtility::UrlEncode(url);

args.name(formstr(MapViewer));
args.parm(strfmt(#BingMapURL, url));
formRun = classFactory.formRunClass(args);

formRun.init();
formRun.run();
formRun.wait();
}

Create Opportunity & Prospect with contacts through code in AX 2012

void clicked()
{
smmOpportunityTable _oppTable;
StaggingOpportunity _stagOpportunity;
ContactPerson _contactPerson;
LogisticsLocation _logisticLocation;
LogisticsElectronicAddress _logisticEAddress, _logisticEAddressI;
DirPartyLocation _dirPartyLocation;
RecId _dirPartyCompanyId, _dirPartyPersonId, _owner;
;

if(StaggingOpportunity.IsInjected == NoYes::No)
{
try
{
ttsBegin;

_dirPartyCompanyId = DirPartyTable::createNew(DirPartyType::Organization, StaggingOpportunity.Company).RecId;
_dirPartyPersonId = DirPartyTable::createNew(DirPartyType::Person, StaggingOpportunity.Name).RecId;
_owner = Hcmworker::findByPerson(DirPersonUser::find(curUserId()).PersonParty).RecId;

//Creation of the opportunity
//On the creation of the opportunity, system will automatically create the Prospect
_oppTable.clear();
_oppTable.Subject = StaggingOpportunity.Subject;
_oppTable.OpenedByWorker = _owner;
_oppTable.OpenedDate = today();
_oppTable.OwnerWorker = _owner;
_oppTable.Party = _dirPartyCompanyId;
_oppTable.Status = smmOpportunityStatus::Active;
_oppTable.insert();

//Creation of Contact detail
_contactPerson.clear();
_contactPerson.ContactForParty = _oppTable.Party;
_contactPerson.MainResponsibleWorker = _oppTable.OwnerWorker;
_contactPerson.Party = _dirPartyPersonId;
_contactPerson.Sensitivity = smmSensitivity::Normal;
_contactPerson.insert();
//Creation of Logistic information as a Parent for Email and Phone data
_logisticLocation.clear();
_logisticLocation.Description = StaggingOpportunity.Company;
_logisticLocation.insert();

//Creation of Email address detail
_logisticEAddress.clear();
_logisticEAddress.Description = “Official Email address”;
_logisticEAddress.IsPrimary = NoYes::Yes;
_logisticEAddress.Location = _logisticLocation.RecId;
_logisticEAddress.Locator = StaggingOpportunity.Email;
_logisticEAddress.PrivateForParty = _oppTable.Party;
_logisticEAddress.Type = LogisticsElectronicAddressMethodType::Email;
_logisticEAddress.insert();

//Associate the data of above data with DirPartyTable
DirPartyTable::updatePrimaryContact(_contactPerson.Party, _logisticEAddress);
//Creation of data in dirPartyLocation
_dirPartyLocation.clear();
_dirPartyLocation.IsPrimary = NoYes::Yes;
_dirPartyLocation.Location = _logisticLocation.RecId;
_dirPartyLocation.Party = _dirPartyPersonId;
_dirPartyLocation.insert();
//Creation of Phone Number detail
_logisticEAddress.clear();
_logisticEAddress.Description = “Official Phone Number”;
_logisticEAddress.IsPrimary = NoYes::No;
_logisticEAddress.Location = _logisticLocation.RecId;
_logisticEAddress.Locator = StaggingOpportunity.Contact;
_logisticEAddress.PrivateForParty = _oppTable.Party;
_logisticEAddress.Type = LogisticsElectronicAddressMethodType::Phone;
_logisticEAddress.insert();
//Update the stagging information
/* while select forUpdate _stagOpportunity
where
_stagOpportunity.RecId == StaggingOpportunity.RecId
{
_stagOpportunity.OpportunityId = _oppTable.OpportunityId ;
_stagOpportunity.IsInjected = NoYes::Yes ;
_stagOpportunity.update();
}*/

update_recordSet _stagOpportunity
setting
OpportunityId = _oppTable.OpportunityId,
IsInjected = NoYes::Yes
where
_stagOpportunity.RecId == StaggingOpportunity.RecId;

ttsCommit;
StaggingOpportunity_ds.refresh();
StaggingOpportunity_ds.refreshEx();
StaggingOpportunity_ds.reread();
}
catch
{
error(strFmt(“%1”, Exception::Error));
}
}
else
warning(“Selected record already exist in the Opportunity section.”);

//super();
}

WORKING WITH CUSTOMIZED AIF (SERVICE OPERATION : UPDATE)

EntityKey[] _entityKeyList = null;

List<CriteriaElement> _criteriaList = new List<CriteriaElement>();
CriteriaElement _criteria = new CriteriaElement();

_criteria.DataSourceName = “ContactTable”;
_criteria.FieldName = “RecID”;
_criteria.Operator = Operator.Equal;
_criteria.Value1 = “5637144586”;

_criteriaList.Add(_criteria);

QueryCriteria _queryCriteria = new QueryCriteria();
_queryCriteria.CriteriaElement = _criteriaList.ToArray();
try
{
_entityKeyList = this.getClientObject().findKeys(this.getCallContext(), _queryCriteria);
AxdContactQuery _contactQuery = this.getClientObject().read(this.getCallContext(), _entityKeyList);

AxdEntity_ContactTable _contactTable = _contactQuery.ContactTable[0];
_contactTable.ContactName = “Ahmed Muhammad Alam”;

this.getClientObject().update(this.getCallContext(), _entityKeyList, _contactQuery);
RTB_ErrorBox.Text = “Updated successfully”;
}
catch(Exception ex)
{
RTB_ErrorBox.Text = ex.Message;
}

Working with customized AIF (Service Operation : Find)

AxdContactQuery _trans = new AxdContactQuery();
CriteriaElement[] criteriaElement = new CriteriaElement[1];
criteriaElement[0] = new CriteriaElement();
criteriaElement[0].DataSourceName = “ContactTable”;
criteriaElement[0].FieldName = “ContactID”;
criteriaElement[0].Value1 = “CNT-0003”;

QueryCriteria queryCriteria = new QueryCriteria();
queryCriteria.CriteriaElement = criteriaElement;

try
{
_trans = this.getClientObject().find(this.getCallContext(), queryCriteria);
lstBox.Items.Add(_trans.ContactTable[0].RecId);
}
catch (Exception ex)
{
RTB_ErrorBox.Text = ex.Message;
}

Working with customized AIF (Service Operation : Delete)

KeyField keyField = new KeyField() { Field = “RecId”, Value = “5637144589” };

// Create an entity key instance and put in the key field data
EntityKey entityKey = new EntityKey();
entityKey.KeyData = new KeyField[1] { keyField };
EntityKey[] entityKeys = new EntityKey[1] { entityKey };

try
{
this.getClientObject().delete(this.getCallContext(), entityKeys);
RTB_ErrorBox.Text = “Deleted successfully.”;
}
catch (Exception ex)
{
RTB_ErrorBox.Text = ex.Message;
}

Working with customized AIF (Service Operation: Read)

ContactQueryServiceClient _client = new ContactQueryServiceClient();
AxdContactQuery _contactQuery = new AxdContactQuery();
EntityKey[] entityKeys = new EntityKey[1];

entityKeys[0] = new EntityKey();
entityKeys[0].KeyData = new KeyField[1];
entityKeys[0].KeyData[0] = new KeyField();
entityKeys[0].KeyData[0].Field = “RecId”;
entityKeys[0].KeyData[0].Value = “5637144586”;
try
{
_contactQuery = _client.read(this.getCallContext(), entityKeys);
lstBox.Items.Add(_contactQuery.ContactTable[0].ContactID + ” ” + _contactQuery.ContactTable[0].ContactName + ” ” + _contactQuery.ContactTable[0].ContactEmail);
}
catch (Exception ex)
{
RTB_ErrorBox.Text = ex.Message;
}