One more definition of Group by for T-SQL

Hi everybody,

today I want to give one more definition of Group by of SQL language which for me is very explanatory.

Group by statement produces a record for each unique combination of group by columns list and ommiting other columns of tables.

Also you can apply aggregate functions to other columns. Aggregate functions may be count, sum, avg, mean, max, etc.

And each of the functions will be executed against columns of groupped records.

Logical sequence of T-SQL operations

Hello everybody,

Today I want to note that SQL Server has following order of processing:

  1. From
  2. Where
  3. Group by
  4. Having
  5. Select
  6. [Expressions]
  7. [Distinct]
  8. Order By
  9. [TOP/OFFSET-FETCH]

By looking on this order you may understand why in C# LINQ from goes first and not select. That is because C# is imperative language, while SQL is declarative.

Four classes of databases systems

Hello everybody,

I want to leave a short note on four types of database systems:

  • OLTP
  • DSA
  • DW
  • ETL

Online transactional processing

This type of systems is used for data entry, basically for CRUD operations. As usually some rules of normalization is applied there. Also OLTP is not suitable for reporting purposes because processing normalized data takes a lot of computing powers. That's why next type of systems is used:

Data warehouses

This kind of systems is used for data-retrieval and reporting purposes. Mainly it's structure is optimized for data-retrieval needs. Quite often tables have redundancy, fewer tables, simpler relationship between tables. You can learn a bit more about data warehouses via googling of data warehouse + star schema, data warehouse + facts table,  data warehouse + data mart, etc. 

ETL

The process of pulling data from OLTP is named extract, transform, load. For such purposes often used Microsoft SQL Server Integration Services ( SSIS ) for handling ETL needs.

DSA

Quite often ETL is implemented not as one step, but in stages, of creating separate stages and locating those stages on separate databases. Then those databases belong to Data Staging Area. 

How to extend Persist of your own graph in Acumatica

Hello everybody,

here I want to leave a short line on how to extend Perist method of your own graph.

I described once long time ago how to override Save action in your extension, but sometime it can be needed to override Persist in your own graph.

Sometime it can be necessary. Below goes sample of code for this purpose:

public override void Persist()
{
	//Some other custom business logic
 
	base.Persist();
}

Here you go.

How to compare Acumatica objects without ObjectsEqual

Hello everybody,

Long time ago I made a post on how to extend if needed method  ObjectsEqual in Acumatica.

today I want to extend my answer a bit more and share with you class OneLevelComparer. With help of this class you may check if some object was changed without need of typing all fields of DAC class. Take a look on the class itself:

/// <summary>
/// Comparison of two objects on equality based on reflection. 
/// Doesn't support inner collections, objects.
/// </summary>
public static class OneLevelComparer
{
	// Item1: property name, Item2 current, Item3 original
	public static List<Tuple<stringobjectobject>> Differences<T>(T current, T original)
	{
		var diffs = new List<Tuple<stringobjectobject>>();
 
		MethodInfo areEqualMethod = typeof(OneLevelComparer).GetMethod("AreEqual"BindingFlags.Static | BindingFlags.NonPublic);
 
		foreach (PropertyInfo prop in typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.Public))
		{
			object x = prop.GetValue(current);
			object y = prop.GetValue(original);
			bool areEqual = (bool)areEqualMethod.MakeGenericMethod(prop.PropertyType).Invoke(nullnew object[] { x, y });
 
			if (!areEqual)
			{
				diffs.Add(Tuple.Create(prop.Name, x, y));
			}
		}
 
		return diffs;
	}
 
	private static bool AreEqual<T>(T x, T y)
	{
		return EqualityComparer<T>.Default.Equals(x, y);
	}
}

Then further in the code you may build your conclusions depending of changes for example like this:

var changes = OneLevelComparer.Differences(currentApSetup, apSetupOriginal);
foreach (Tuple<stringobjectobject> change in changes)
{
	if(change.Item1 == "tstamp")
		continue;
	if(change.Item1 == "LastModifiedDateTime")
		continue;

etc...

Closest possible to database approach in Acumatica

Hello everybody,

today I want to leave another post about internals of Acumatica, about which you may heard something like "Never try it at home". In another words, sometime you may need to do the following in Acumaitca:

  1. Insert directly into database ( for this purpose I'd recommend you to use PXDatabase.Insert )
  2. Alter Table
  3. Alter Schema of Table
  4. Create column
  5. Drop column
  6. Drop Table
  7. .... this list is not exhaustive

and any other system level activities. By default and for SOLId SOLID SOLID reasons you can't achieve it. But sometime you may need it. 

Below goes sample of code, which you can use for setting identity insert to on, and then execute some update onf the table, and then assign some value to it:

using System.Collections;
using System.Collections.Generic;
using PX.BulkInsert.Installer;
using PX.Data;
using PX.DbServices.Commands;
using PX.DbServices.Commands.Data;
using PX.DbServices.Points;
using PX.DbServices.Points.DbmsBase;
using PX.DbServices.Points.MsSql.Commands;
using PX.DbServices.QueryObjectModel;
using PX.Objects.PO;
using PX.Objects.SO;
 
 
namespace SomeSqlDemo
{
	public class POOrderEntryExt : PXGraphExtension<POOrderEntry>
	{
		public IEnumerable ExecuteSomeSql(PXAdapter adapter)
		{
			string tableName = "SOShipLineSplit";
			int companyId = 2;
			PointDbmsBase point = PXDatabase.Provider.CreateDbServicesPoint();
			ScriptExecutor scriptExecutor = new ScriptExecutor(point);
			ExecutionContext executionContext = new ExecutionContext(new SimpleExecutionObserver());
 
			point.executeCommands(new List<CommandBase>()
				{
					new CmdIdentityInsert(true) { TableName = tableName },
					
					new CmdUpdate(tableName)
					{
						Condition = Yaql.companyIdEq(companyId, point.getCompanies(), point.Schema.GetTable(tableName).HasCompanyMask(), 
						tableName).and(
							Yaql.column<SOShipLineSplit.shipmentNbr>().eq(Yaql.constant("000021"))).and(
							Yaql.column<SOShipLineSplit.lineNbr>().eq(Yaql.constant(1))).and(
							Yaql.column<SOShipLineSplit.splitLineNbr>().eq(Yaql.constant(2))),
 
						AssignValues = { { "UsrUniqueID"Yaql.constant(777) } }
					}
				},
				executionContext);
 
			return adapter.Get();
		}
	}
}

Besides that take a look on this screenshot:

as you can see from the screenshot I didn't mention even third part of available methods, which you can try sometimes in case if you really need to go hard core. Deeply hardcore.

Summary
As I mentioned already Yaql is not something, where you should go straight away. Mainly you can use that features as some kind of latest resort, which will migrate data of your customization/plugin between different versions of Acumatica or if you want to preinit tables of your customization with some data in C# code etc. For other cases I'd rather recommend to use BQL with SQL Scripts for this purpose.

How to call RecordCCPayment action from Screen Based Web API call in Acumatica for Payments and applications screen

Hello everybody,

today I want to write a few words with code samples on how to work with Screen based web API in Acumatica. 

Stage preparation

Before you go, first step that is needed is to have Acumatica instance ready. So install Acumatica with sales demo database.

As mentioned in the title of the article, I'm going to work with "Payments and Applications" screen ( AR302000 ) and with Action Record CC Payment: 

As usually in cases of WEB API calls in .Net, you'll need somehow to create Web Reference. 

For Record CC Payment following sequence of Actions is needed.

  1. In Acumatica instance navigate to page AR302000, and click there on Help -> Web Service:

2. Copy into clipboard url, of the opened window. 

3. Go to Visual Studio and create there some Application. For example console app.

4. Then click on References with right mouse click and in pop up choose Add Service Reference:

5. In window that will appear click on Advanced

6. In next window click on "Add Web Reference... " which will cause appearing one more window:

Initially left window will be opened, and then window at the right will be opened.

After all of those steps, you can type following code for your Main function:

 
		static void Main(string[] args)
		{
			ServicePointManager.ServerCertificateValidationCallback += new System.Net.Security.RemoteCertificateValidationCallback(
				(s, c, ch, pe) => { return true; });
 
			using (Screen scr = new Screen())
			{
				scr.CookieContainer = new CookieContainer();
				var lr = scr.Login("admin""123");
				if (lr.Code == ErrorCode.OK)
				{
					try
					{
						var schema = scr.GetSchema();
 
						var commands = new Command[]
						{
							schema.Actions.Insert,
							new Value()
							{
								Value = "BESTYPEIMG",
								LinkedCommand = schema.PaymentSummary.Customer
							},
 
							
							new Value()
							{
								Value = DateTime.Now.ToString("yyyy/MM/dd hh:mm:ss"),
								LinkedCommand = schema.PaymentSummary.Description
							},
 
							new Value()
							{
								Value = "770",
								LinkedCommand = schema.PaymentSummary.PaymentAmount
							},
 
                                                        // if configured to prompt, then must set DialogAnswer
                                                        new Value()
                                                        {
                                                                 Value = "paymentreffdsafas",
                                                                 LinkedCommand = schema.PaymentSummary.PaymentRef
                                                        },
  
                                                        new Value()
							{
								Value = "xxx",
								LinkedCommand = schema.RecordCCPaymentCCPaymentData.PCTranNumber
							},
							new Value()
							{
								Value = "xxx",
								LinkedCommand = schema.RecordCCPaymentCCPaymentData.AuthNumber
							},
							new Value()
							{
								Value = "OK",
								LinkedCommand = schema.PaymentSummary.ServiceCommands.DialogAnswer,  // Base code shows that it is PaymentSummary (Document.Ask())
                                                                Commit = true
							},
							schema.Actions.RecordCCPayment
                                               };
 						scr.Submit(commands);
 
						var status = scr.GetProcessStatus();
						while (status.Status == ProcessStatus.InProcess)
						{
							System.Threading.Thread.Sleep(1000);
							status = scr.GetProcessStatus();
						}
 
						if (status.Status == ProcessStatus.Completed)
						{
							commands = new Command[] {
								schema.CreditCardProcessingInfo.TranNbr,
								schema.CreditCardProcessingInfo.ProcCenter,
								schema.CreditCardProcessingInfo.TranType,
								schema.CreditCardProcessingInfo.TranStatus,
								schema.CreditCardProcessingInfo.PCResponseReason,
								schema.CreditCardProcessingInfo.TranTime,
								schema.CreditCardProcessingInfo.ProcStatus,
								schema.PaymentSummary.ReferenceNbr
							};
 
							var data = scr.Submit(commands);
						}
					}
					finally
					{
						scr.Logout();
					}
				}
			}
		}

Summary

In presented code I want to point your attention to those details:

1. This line Snippet

using (Screen scr = new Screen())

 

will help you to make efficient memory usage, especially for cases when you need to submit a lot of payments

2. try/finally combination or try/catch/finally combination will help you to track error messages

3. This line

Snippet

while (status.Status == ProcessStatus.InProcess)

 

will help you necessary amount of time for request result.

4. In case if status.Status will be equal to aborted, or something similar, you can use status.Message and check what is there. 

5. Quite often it is needed to know some values, that was created during process. For this purpose you may use commands list one more time with sycn request as done in the block

Snippet

if (status.Status == ProcessStatus.Completed)

 

6. Always, I repeat always call Logout method. Otherwise you'll get error message that you've riched maximum allowed connections for your account.

Submition types in Acumatica

Hello everybody,

today I want to leave a really short notice on how you can submit data into Acumatica. There are three ways:

  • Contract-based REST API
  • Contract-based SOAT API
  • Screen-based SOAP API

Historically the first was screen based SOAP API, and with time two others were added. Later on I hope to add description of others as well.

Where log of Visual Studio is located

Hello everybody,

today I want to leave a short note on where to search for log file of Visual Studio. 

It lives here:

C:\Users\{USER}\AppData\Roaming\Microsoft\VisualStudio .....\{visual studio version}\ActivityLog.xml

Whenever I speak about log files with any kind of developer, I see round eyes and trembling voice with a question, why on earth should I look into log file?

The reason is simple, sometime you may get error message like this: "Error HRESULT E_FAIL has been returned from a call to a COM component" during adding reference to your class library. 

What may stand behind that error message? No ideas? The same was with me, but after looking into log file, we have found there following statement:

Microsoft.visualstudio.shell.interop.IVsReferenceManager2 within the Microsoft.VisualStudio.Shell.Interop.11.0.dll

and some additional close to abra cadabra statements, but googling that error message was much easier. 

Finally we applied following steps:

#1 Open "Developer Command Prompt for VS 2017" as Admin

#2 CD into "C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\Common7\IDE\PublicAssemblies"

#3 Run "gacutil -i Microsoft.VisualStudio.Shell.Interop.11.0.dll"

Summary

Remember in which location lives log of Visual Studio. With it's help you'll be able to make miracles related to bugs. Another idea to keep in mind, take Microsoft as a pattern, and have your own log, especially of errors in your apps. 

How to refresh cache of Acumatica

Hello everybody,

today I want to leave a comment on how to refresh cache of Acumaitca.

As usually I start with this method:

 

ViewName.View.RequestRefresh();

 

But I found that it not always work. For my surprise RequestRefresh not works for some reasons. I think reason for this may be that Acumatica has two caches: caching of data and caching of queries.  

If that is the case, I use another approach ( more hardcore ) 

 

ViewName.View.Cache.Clear(); // clearing cached data

ViewName.View.Cache.ClearQueryCache(); // clearing cached queries.

 

Second approach works a bit better, because it clears not only data, but also clears cached queries, and as outcome clearing results