Hi Everyone,
Promised to write few posts about Web Services...
Getting large volume of data via Generic Inquiry Endpoint
Prerequisite - no knowledge about Acumatica platform whatsoever :), common sense and C# as a newbee.
First of all I would create a Generic Inquiry in Acumatica to get our data ready.
Please note that in order to get data without timeout errors, it must be arranged in batches.
Due to the fact that Acumatica
does not handle Rowcount in GI's well enough, and we can't relay on it, so we would need to arrange our data in a table properly
BY OURSELVES.
I tried few scenarios and it looks like getting data in batches of 1000 lines works the best way.
WEB service does not die out and process is not getting interrupted.
So, step by step now.
1. Please create a GI (Generic Inquiry) first.
Silly example below:
Please take note, I have added
two special columns, Processing Batch and Maximum Batch Number.
This is to organize WEB service better, later you will see why do we need these two.
In short - it will minimize server calls and facilitate uninterrupted data flow.
2. Look at the design screen for this GI:
a) Data in the GI must be sorted by Processing Batch. In order to appear in the orderly manner for export. This is important!
b) Put some conditions in the GI so you have only that number of rows that you need. In my case getting 112 batches 1000 rows each, looks scary already... 112k records...
c) create underlying View or Table in the database to reflect Processing Batch and Maximum Batch numbers correctly. Correctly means have a Primary Key, don't make table too wide to facilitate speed of data extraction.
d) Convert that table into a Data Access Class (DAC) to then later use in the GI as a data source (hint - use my older blog to find how to convert view/table to DAC)
e) Format GI nicely so when you look at it, you experience proud and pleasure. :)
Regarding those special fields:
Processing Batch - is a number (int), representing a chunk of lines, by 1000 lines each.
Maximum Batch - is the last Processing Batch number (int).
Note: "Maximum Batch" number is the same for all rows, while "Processing Batch" number is changing for every thousand of rows.
In order to create such numbering in a table I used a process on SQL, in that update, LineNbr is just a Primary Key, you can use any other PK actually:
update MYTABLE set BatchNbr = s.BatchNbr,
[MaxBatchNbr] = (select FLOOR(count(*)/1000)+1 from MYTABLE)
from MYTABLE p
inner join (select BatchNbr = FLOOR(ROW_NUMBER() OVER(ORDER BY LineNbr ASC)/1000)+1,LineNbr,CompanyID from MYTABLE) s
on p.CompanyID = s.CompanyID and p.LineNbr = s.LineNbr
Again, you will see later, why we need these numbers to be IN an SQL TABLE itself.
Just accept it for now, ok.
3. Give that GI Access rights in Acumatica.
So the user we use for the web service, will be able to see it. Place it somewhere on your Site Map. Important, I call our GI
CRMPAY
4. Create the Endpoint.
Insert CRMPAY there. Add fields. Please note our special fields are there too:
5. Click VIEW ENDPOINT SERVICE button to get a link to the web service.
Just copy the link to somewhere to keep, from the address bar, it will be something like: https://yourserver/yourendpointname/endpointversion?wdsl
6. Open your Visual Studio and create a new template
Project type C# Console, give it a name, I called it BLOG.
7. In order to connect to your web service via https we will need to add some code to the project.
Open Program.cs in your project and add this block to it:
Before:
After. What was added is in BLUE:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Net;
using System.Net.Security;
using System.Security.Cryptography.X509Certificates;
namespace BLOG
{
class Program
{
static void Main(string[] args)
{
//This code is necessary only if you connect to the website
//through the HTTPS connection and
//you need to use custom validation of an SSL certificate
//(for example, if the website uses a self-signed certificate).
ServicePointManager.ServerCertificateValidationCallback += new
RemoteCertificateValidationCallback(ValidateRemoteCertificate);
using (DefaultSoapClient soapClient = new DefaultSoapClient())
{
//Log in to Acumatica ERP
soapClient.Login
(
Properties.Settings.Default.UserName,
Properties.Settings.Default.Password,
Properties.Settings.Default.CompanyName,
Properties.Settings.Default.Branch,
null
);
try
{
//You will add the integration code here
CRMDataRetriever.ExportCRMPayment(soapClient);
}
catch (Exception e)
{
Console.WriteLine(e);
Console.WriteLine();
Console.WriteLine("Press any key to continue");
Console.ReadLine();
}
finally
{
//Log out from Acumatica ERP
soapClient.Logout();
}
}
}
//Callback, which is used to validate the certificate of
//an Acumatica ERP website in an SSL conversation
private static bool ValidateRemoteCertificate(object sender,
X509Certificate cert, X509Chain chain, SslPolicyErrors policyErrors)
{
//For simplicity, this callback always returns true.
//In a real integration application, you must check
//an SSL certificate here.
return true;
}
}
}
}
8. You have noticed, Visual Studio prompting that some parts of the code above are not in yet:
It is time to add web service to the Project. Click Add Service Reference:
Then indicate the link from Item 5. And give it a name. Click OK:
You may be prompted for a certificate mismatch, just accept it.
9. Add a namespace to the top portion of the Program block and you will immediately notice some errors are gone:
10. Do 1,2,3,4 as displayed. Just right click at your project then do 1,2,3,4 actually:
11. Now we should provide some security references here to get logged into Acumatica. So just add four items exactly as below, then save project:
12. And, we supposed to get left with the only Error. Will be fixing that one then:
13. Lets create that class CRMDataRetriever, where we are going to add the code:
a) Add the class:
b) Give it a name and Add:
c) Add into the top section of the class these two namespaces:
using BLOG.BLOGSERVICE;
using System.IO;
d) Insert code into the class. In my case code will retrieve the first Batch, then one by one subsequent:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BLOG.BLOGSERVICE;
using System.IO;
namespace BLOG
{
class CRMDataRetriever
{
public static int? processBatchNbr = 0;
//Retrieving the list of CRM Payment Items
public static void ExportCRMPayment(DefaultSoapClient soapClient)
{
Console.WriteLine("Retrieving the list of Students...");
var crmPaymentsToBeFound = soapClient.GetList(
new CRMPAY
{
//Specify return behavior - we are getting ALL the fields
ReturnBehavior = ReturnBehavior.All,
//Filter the items by the BatchNumber
BatchNbr = new IntSearch
{
Condition = IntCondition.Equal,
Value = 1
}
});
//I am writing it into a csv file
using (StreamWriter file = new StreamWriter("CRMPayments.csv"))
{
//Add headers to the file
file.WriteLine(
"BatchNbr,LineNbr,StudentCRMID,Term,Year,CourseID,PaymentStatus,");
//This is maximum batch to process and writing batch one
foreach (CRMPAY crmPaymentsToBeFoun in crmPaymentsToBeFound)
{
processBatchNbr = crmPaymentsToBeFoun.MaxBatchNbr.Value;
Console.WriteLine("Retrieving the First Record ...");
//Write the values for each item
file.WriteLine(string.Format("{0},{1},{2},{3},{4},{5},{6},",
crmPaymentsToBeFoun.BatchNbr.Value,
crmPaymentsToBeFoun.LineNbr.Value,
crmPaymentsToBeFoun.StudentCRMID.Value,
crmPaymentsToBeFoun.Term.Value,
crmPaymentsToBeFoun.Year.Value,
crmPaymentsToBeFoun.CourseID.Value,
crmPaymentsToBeFoun.PaymentStatus.Value));
}
while (processBatchNbr != 1)
{
CRMPAY filter = new CRMPAY
{
BatchNbr = new IntSearch
{
Condition = IntCondition.Equal,
Value = processBatchNbr
},
ReturnBehavior = ReturnBehavior.All
};
Entity[] crmPayments = soapClient.GetList(filter);
processBatchNbr = processBatchNbr - 1;
//Write the values for each item
foreach (CRMPAY crmPayment in crmPayments)
{
file.WriteLine(string.Format("{0},{1},{2},{3},{4},{5},{6}",
crmPayment.BatchNbr.Value,
crmPayment.LineNbr.Value,
crmPayment.StudentCRMID.Value,
crmPayment.Term.Value,
crmPayment.Year.Value,
crmPayment.CourseID.Value,
crmPayment.PaymentStatus.Value));
Console.WriteLine("Subsequent Rercord written into a file...");
}
}
}
}
}
14. Please note every time we call web service, we know what will be the next batch from the processBatchNbr variable, which we set once we retrieved the first record. Within a batch of records we follow based on the BatchNbr field condition. We do not do any calls to the web service which are redundant or unnecessary.
15. In order for WEB service to login correctly we have to enable cookies in the project config file. We should do it manually.
a) Open app.config file in the notepad
b) Modify it slightly to allow cookies at the areas highlighted. Save. Close:
16. Build the project, get BLOG.exe file at the bin/Debug folder and run it.
Enjoy csv file getting produced and endless messagess popping up at the console :)
All the best,
Sergey.
P.S. Next blog will be about using OData to get the BIG DATA via WEB service of course...
