Hi All,
Today want to share with you one complex requirement that can be archived with Acumatica Cloud xRP platform.
Let me describe scenario first. Lets assume we have customers certification system and we want to track their achievements. For each customer we have several persons (contacts) that want to do certification. Each person can choose one or several courses, depend on his knowledge, position and interest. Depend on each course person needs to complete one or more exams. For some coerces you may choose which of available exams do you want to complete (group 1 or group 2 of exams). And some of these groups are required and some are not.
Here I will provide diagram that illustrate this:
So the problem here that you have multiple dimensions – multiple Contacts, multiple Courses, multiple groups. If you want to get information about how many guys was certified in each company you should do several selects for each contact, course and group. This may significantly degrees performance of any report or inquiry screen.
To keep performance great, it is better to get all data in one select. Luckily Microsoft SQL Server supports very nice feature – Select for XML. This can help us to selects all dimensions and analyze it on server side.
A SELECT query returns results as a rowset, but you can optionally retrieve formal results of a SQL query as XML by specifying the FOR XML clause in the query. The FOR XML clause can be used in top-level queries and in sub queries. Read more here.
Lets check one example
SELECT BAccountID,AcctCD, AcctName,
(SELECT * FROM Contact Contact
WHERE BAccount.CompanyID = Contact.CompanyID
AND BAccount.BAccountID = Contact.BAccountID
FOR XML PATH('v'))
FROM BAccount BAccount
WHERE BAccount.Type = 'PR'
This Example will return data like this:
But how to use it in Acumatica?
First of all we need to develop new attribute that will generate sub select with FOR XML parameter a and put it into the DACs field.
New attribute should:
- Be inherited form PXDBFieldAttribute, as we will read this field form DB.
- Have RowSelecting event handler to read data from data reader.
- Have CommandPreparing event handler to change query and append FOR XML parameter.
public class PXDBSubSelectForXMLAttribute : PXDBFieldAttribute, IPXRowSelectingSubscriber, IPXCommandPreparingSubscriber { }
Also attribute should accept sub-select query and restrict fields that are will be selected with sub-select to male XML simpler.
public PXDBSubSelectForXMLAttribute(Type select, params Type[] columns)
{ }
Example or attribute definition:
[PXDBSubSelectForXML(typeof(Select2<Contact,
InnerJoin<UNContactInUniversity,
On<UNContactInUniversity.contactID, Equal<Contact.contactID>>,
InnerJoin<UNContactTest,
On<UNContactTest.universityID, Equal<UNContactInUniversity.universityID>>>>,
Where<Contact.bAccountID, Equal<BAccount.bAccountID>,
And<UNContactTest.passed, IsNotNull>>>),
typeof(Contact.contactID),
typeof(UNContactTest.testCD),
typeof(UNContactTest.passed))]
public String Certification { get; set; }
This example will read all certification information from associated with current BAccount table. Attribute will select form database only specified as parameters fields (Contact.contactID, UNContactTest.testCD, UNContactTest.passed), other fields will be skipped form selection.
And as addition it should provide functionality to read XML data as PXResult with all DACs specified as tables in sub-select command. This behavior is standard for Acumatica.
Attribute will create a custom PXDataRecord (PXDataRecordXML) that will read all specified in attribute fields form XML. Other (Not specified in definition) fields will be filled with null. After reading data attribute will created DAC instance and rise RowSelecting events.
List<PXResult> data = PXDBSubSelectForXMLAttribute
.GetValues<PartnerStatistics, PartnerStatistics.certification>(sender, row);
foreach (Contact contact in data.Select(r => r.GetItem<Contact>()))
{
//do something
}
As in addition attribute also can display XML data in specified format:
DisplayFormat = “Contact ID = {Contact.contactID}, Test CD = {UNContactTest.testCD}, Passed = {UNContactTest.passed}”
Please not that names of tables and fields are case sensitive, so be careful with names.
Several addition parameters can help you to control displaying of values better:
DisplayAppendColumns – parameter will add columns that are not in Display format automatically.
DisplayInsertNulls – will display value even if it is null.
Update from 18/01/2018. Please note SQL generation on Command Preparing had been changed in version 2018R2 to the object model. This example has been updated to meet requiranments of Acumatica 2018R2. If you need to see previous version, please refer to Revision 2 of GitHub Gist file
Full attribute code snippet:
Just as a funny thing:
Here you can see a SQL query that was generated for inquiry screen that I had developed. This includes select for XML.
Looks simple! 🙂
Have a nice development!