Optimize amount of read data with Acumatica and LINQ

Hi everybody,

today I want to share quite useful post for cases, if you'll need to get some limited dataset from Acumatica database. 

Take a look on the code below:

public PXAction<SOOrder> FilterColumns;
[PXButton]
[PXUIField(DisplayName = "Filter Columns")]
public IEnumerable filterColumns(PXAdapter adapter)
{
    var arTrans = SelectFrom<ARTran>.View.Select(Base).
            Where(t => t.Record.TranType == "CRM")
            .Select(r => new { r.Record.TranType, r.Record.RefNbr, r.Record.CuryTranAmt });
 
    Base.CurrentDocument.Current.DocDesc += arTrans.Sum(a => a.CuryTranAmt).ToString();
    Base.CurrentDocument.Cache.Update(Base.CurrentDocument.Current);
 
    return adapter.Get();
}

As you may notice, it reads from table ARTran, so nothing special. But tell me please, what columns will be read? All of ARTran columns? Three columns? You'll be surprised to learn, that only one column will be read. And that will be aggregated column.

Below goes a screenshot from SQL Server profiler:

as you see, in the output, Acumatica ORM generated single value and single column.

Summary

If you need optimize reading from database, then consider to use ordinary LINQ, as Acumatica ERP become more efficient.

 

 

Acumatica: SMS Provider, Twilio SMS provider, send SMS in action

Hello everybody,

Today I want to share one approach, how to send SMS message from custom action in Acumatica.

Acumatica has several sms providers in SalesDemo data base, it depends from Acumatica’s version, so, we will use Twilio provider. On SMS Provider screen you can find authorization parameters from each provider, as on screen shot from (22r1 build):

First you need to add two references from Acumatica’s Bin folder to your project in VS:

PX.SmsProvider.Core.dll

PX.SmsProvider.UI.dll

 

Then create graph extension for any screen that you need and develop next logic in custom button “Send SMS”.

Also, you need to use Dependency Injection (ASP.net) and IReadOnlyDictionary interface and define field in graph extension with ISmsProvider type.

The main logic consists from next steps: prepare list of settings (List<ISmsProviderSetting>), create SmsProvider fabric (using dependency injection), load setting to fabric, prepare SMS message and send it in async mode. Also, you can add PXLongOperation feature in action, it doesn’t have conflicts with async method.

Source code example here:

public class SOOrderEntryExt : PXGraphExtension<SOOrderEntry>
{
    public static bool IsActive() => true;
 
 
    [InjectDependency]
    internal IReadOnlyDictionary<string, ISmsProviderFactory> ProviderFactories { get; set; }
    private ISmsProvider _currentProvider;
 
 
    public PXAction<SOOrder> SendSMS;
    [PXButton()]
    [PXUIField(DisplayName = "Send SMS", MapEnableRights = PXCacheRights.Select, MapViewRights = PXCacheRights.Select)]
    protected virtual void sendSMS()
    {
        SendSMSNotification(this.Base, "+380990123456", "Test SMS message from Acumatica server");
    }
 
    public virtual void SendSMSNotification(PXGraph graph, string phone, string smsMessage)
    {
        string parsePhoneTo = phone.Trim(' ', '(', ')', '-');
 
        var selectProviderAuth = SelectFrom<SmsPluginParameter>.InnerJoin<SmsPlugin>.On<SmsPluginParameter.pluginName.IsEqual<SmsPlugin.name>>.
            Where<SmsPlugin.isDefault.IsEqual<@P.AsBool>>.View.Select(graph, true);
 
        string ACCOUNT_SID = string.Empty;
        string SECRET = string.Empty;
        string FROM_PHONE_NBR = string.Empty;
 
        var setting = new List<TGSmsProviderSettings>();
 
        foreach (SmsPluginParameter item in selectProviderAuth)
        {
            if (item.PluginTypeName != "PX.SmsProvider.Twilio.TwilioVoiceProvider")
                throw new PXException("No preferences for Twilio provider!");
 
            switch (item.Name)
            {
                case nameof(ACCOUNT_SID):
                    {
                        ACCOUNT_SID = item.Value;
                        setting.Add(new TGSmsProviderSettings()
                        {
                            Name = nameof(ACCOUNT_SID),
                            Value = ACCOUNT_SID,
                        });
                        break;
                    }
                case nameof(SECRET):
                    {
                        SECRET = item.Value;
                        setting.Add(new TGSmsProviderSettings()
                        {
                            Name = nameof(SECRET),
                            Value = SECRET,
                        });
                        break;
                    }
                case nameof(FROM_PHONE_NBR):
                    {
                        FROM_PHONE_NBR = item.Value;
                        setting.Add(new TGSmsProviderSettings()
                        {
                            Name = nameof(FROM_PHONE_NBR),
                            Value = FROM_PHONE_NBR,
                        });
                        break;
                    }
            }
        }
 
        if (string.IsNullOrEmpty(ACCOUNT_SID) || string.IsNullOrEmpty(FROM_PHONE_NBR) || string.IsNullOrEmpty(SECRET))
            throw new PXException("No preferences for Twilio provider!");
 
        SendSmSMessage(setting, parsePhoneTo, smsMessage);
    }
 
    private void SendSmSMessage(List<TGSmsProviderSettings> settings, string phone, string body)
    {
        if (this._currentProvider != null) return;
 
        this._currentProvider = this.ProviderFactories["PX.SmsProvider.Twilio.TwilioVoiceProvider"].Create();
        this._currentProvider.LoadSettings(settings);
 
        var messageRequest = new SendMessageRequest()
        {
            RecepientPhoneNbr = phone,
            RecepientSMSMessage = body
        };
 
        try
        {
            _currentProvider.SendMessageAsync(messageRequest, CancellationToken.None).Wait();
        }
        catch (AggregateException ex)
        {
            string str = string.Join(";", ex.InnerExceptions.Select(x => x.Message));
            throw new PXException(str);
        }
    }
}
 
 
public class TGSmsProviderSettings : ISmsProviderSetting
{
    public string Name { get; set; }
    public string Description { get; set; }
    public string Value { get; set; }
}

Summary

With provided code and Twilio you can send sms messages to your USA based customers. Similar activites may be done for other customers, but out of the box Acumatica allows to use Twilio.

How to open windows desktop applications from Acumatica

Imagine you are working on Acumatica customization that needs to integrate with an existing desktop application. How can you launch the desktop application from the web-based app? It might seem impossible at first, but on Windows, it's actually quite simple. The key is to use Custom Protocol Handlers. All you need to do is install a new custom protocol and tell Windows which application should handle it. For example, let's say you have a desktop application that performs sales analysis based on the stock item when it is launched. You can create a new custom protocol called " ItemAnalyzer://" and whenever a URL with this protocol is entered into the browser, the desktop application will be launched and the text after the protocol will be treated as a parameter.

 

It's important to note that when using protocol handlers, the protocol name itself will be included as part of the argument passed to the desktop application. This may require some additional processing to remove the protocol name (such as the "GetStringBetweenDelimiters" function on line 17). For example, if you run the desktop application with an argument, you might get something like the following:

class Program
{
    static void Main(string[] args)
    {
        string inventoryCD = GetStringBetweenDelimiters(args[0]);
 
        Console.WriteLine($"Processing...: {inventoryCD}");
        // ...
        // do something with inventoryCD
        // ...
 
        Console.WriteLine("Press any key to exit.");
        Console.ReadKey();
    }
 
    static string GetStringBetweenDelimiters(string input)
    {
        int firstIndex = input.IndexOf("://") + 3;
        int lastIndex = input.IndexOf('/', firstIndex);
        return input.Substring(firstIndex, lastIndex - firstIndex);
    }
 
    static void RegProtocol()
    {
        var key = Registry.ClassesRoot.CreateSubKey("ItemAnalyzer");
        
        key.SetValue("", "URL:ItemAnalyzer Protocol");
        key.SetValue("URL Protocol", "");
        
        var subKey = key.CreateSubKey(@"shell\open\command");
        var execPath = Path.Combine(System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location),
                                    System.Diagnostics.Process.GetCurrentProcess().MainModule.FileName);
 
        subKey.SetValue("", $"{execPath} %1");
        subKey.Close();
        key.Close();
    }
}

To make the magic happen, we need to register the custom protocol handler in the Windows registry. This can be done manually or automatically. First, let's do it manually. To do this, open the Windows registry as a system administrator (type "Regedit" in the start menu or run it as a command). Then, follow these steps:

 

  1. Under HKEY_CLASSES_ROOT, create a new key with the same name as the protocol (in this case, " ItemAnalyzer ").
  2. Inside the new key, add a default new string value with no name (just "Default") and set its content to "URL:protocol_name Protocol" (in this case, "URL: ItemAnalyzer Protocol").

3. Add a new string with the name "URL Protocol" and no content.

4. Under the " ItemAnalyzer" key, add the following keys hierarchically: shell\open\command

5. Inside the "command" key, add a new string with an empty name (just "Default") and set its value to the location of the executable followed by %1, which represents the argument to pass to the executable.

After completing these steps, if you open the run window and type "ItemAnalyzer:// " and press enter, the application will be launched. You can also do this from the browser, and the browser will prompt you for confirmation before launching the application.

Now, let's proceed to the implementation of an action within Acumatica that will initiate the opening of a desktop application when activated. Specifically, we want to create an action on the Sales Order screen that, will execute a specified program to run our desktop application and pass the InventoryCD as an argument.

namespace AcuStockItemAnalizer
{
    public class SOOrderEntryExt : PXGraphExtension<SOOrderEntry>
    {
        public static bool IsActive() => true;
 
        #region Action
        public PXAction<SOOrder> RunStockItemAnalyzer;
        
        [PXUIField(DisplayName = "Run Item Analyzer")]
        [PXButton(CommitChanges = true)]
        protected virtual IEnumerable runStockItemAnalyzer(PXAdapter adapter)
        {
            var tranRow = Base.Transactions.Current;
            if (tranRow != null)
            {
                var inventoryItem = PXSelectorAttribute.Select<SOLine.inventoryID>(Base.Caches[typeof(SOLine)], tranRow, tranRow?.InventoryID) as InventoryItem;
                if (inventoryItem != null)
                {
                    string urlProtocol = string.Format($"ItemAnalyzer://{inventoryItem.InventoryCD}");
                    throw new PXRedirectToUrlException(urlProtocol, null);
                }
            }
            
            return adapter.Get();
        }
        #endregion
    }
}
As demonstrated in the example, activating the action initiates the opening of a desktop application, with the InventoryCD being passed as a parameter.


To automate the process of registering a custom protocol handler, you can change the registry during the application installation or have the application do it automatically. One way to do this in C# is to use the code provided.

Another option is to create a .REG file. This is a plain text file with a .REG extension that contains registry entries, and it can be used to add or modify registry entries automatically when opened. When you double-click a .REG file, it will be imported into the registry, and the registry entries it contains will be added or modified. This can be a convenient way to automatically register custom protocol handlers without manually editing the registry.

 

Windows Registry Editor Version 5.00

 

[HKEY_CLASSES_ROOT\ItemAnalyzer]

@="URL: ItemAnalyzer Protocol"

"URL Protocol"=""

 

[HKEY_CLASSES_ROOT\ItemAnalyzer\shell]

 

[HKEY_CLASSES_ROOT\ItemAnalyzer\shell\open]

 

[HKEY_CLASSES_ROOT\ItemAnalyzer\shell\open\command]

@="\"C:\\TestApplication\\StockItemAnalizer.exe\" \"%1\""

 

How to inject delegate for PXFilteredProcessingJoin and similar classes

Hi,

want to share code, which was written in the context of this question on https://community.acumatica.com. Question is how to modify request and use filtering with help of In or IsIn operator of Acumatica framework. 

After plenty of trial and error, here is the code, with which I've come:

public class INReplenishmentFilterExt : PXCacheExtension<PX.Objects.IN.INReplenishmentFilter>
{
    #region UsrWarehouse
    [PXSelector(typeof(INSite.siteCD), typeof(INSite.siteCD), typeof(INSite.descr), ValidateValue = false, DescriptionField = typeof(INSite.siteCD))]
    [PXUIField(DisplayName = "Warehouse")]
 
    public virtual string UsrWarehouse { get; set; }
    public abstract class usrWarehouse : PX.Data.BQL.BqlString.Field<usrWarehouse> { }
    #endregion
}
 
public class INReplenishmentCreate_Extension : PXGraphExtension<PX.Objects.IN.INReplenishmentCreate>
{
    public override void Initialize()
    {
        base.Initialize();
        BqlCommand cmd =
            new SelectFrom<INReplenishmentItem>();
        var f1 = new PXSelectDelegate(
            () =>
            {
                return records1(Base);
            });
        Base.Views["Records"] = new PXView(Base, false, cmd, f1);
    }
 
    public virtual IEnumerable records1(PXGraph graph)
    {
        var cr = Base.Filter.Current;
        if (cr != null)
        {
            var ext = cr.GetExtension<INReplenishmentFilterExt>();
 
            var objs = ext.UsrWarehouse.Split(';').ToList().Select(a => a.Trim()).ToArray<String>();
            var listResults = new List<INReplenishmentItem>();
 
            var warehouses = SelectFrom<INSite>.Where<INSite.siteCD.IsIn<@P.AsString>>.View.Select(graph, new[]{ objs}).ToList(100);
 
            var wsIds = warehouses.Select(a => a.GetItem<INSite>().SiteID).ToList();
 
            return SelectFrom<INReplenishmentItem>.Where<INReplenishmentItem.siteID.IsIn<@P.AsInt>>.View.Select(graph, wsIds.ToArray());
        }
        else
        {
            return Base.Records.Select(Base);
        }
    }
}

Want to highlight usage of PXSelectDelegate. With it's usage, you can inject any kind of business logic into your graph extension.

Summary

 

 As Gabriel Michaud once pointed, with great power comes great responsibility. Use this code with carefullness, as you may introduce bugs, especially if couple more packages are running along with yours.

PXAggregateAttribute usage for saving of your development time

Hi everybody,

today I want to share one of the insights from code and code, which was conducted by Stéphane Bélanger, and which seems useful, also may be controversial. But still, you may like it. So let me introduce or re-introduce PXAggregateAttribute .

 

If to sum up, purpose of PXAggregateAttribute, is merging of couple of attributes into single one. Consider following situation. You need to have selector Active customer over multiple places in Acumatica: at Purchase orders form, and Sales order form and at Shipment form. And difference between them will be zero, or close to that. Wouldn't that be nice, to declare this attribute in one place, and then to re-use it everywhere else? Of course yes. And for that purpose, Acumatica introduced attribute PXAggregateAttribute. 

Below goes code sample, of how that can be used, and re-used:

[PXInt()]
[PXUIField(DisplayName = "Active Customer")]
[PXDefault()]
[PXSelector(typeof(Search<BAccountR.bAccountID,
    Where<BAccountR.status, Equal<BAccount.status.active>>>))]
public class ActiveCustomer : PXAggregateAttribute {}
 
 
public class SOShipmentExt : PXCacheExtension<SOShipment>
{
    public abstract class activeCustomer : PX.Data.BQL.BqlInt.Field<activeCustomer> { }
 
    // Acuminator disable once PX1030 PXDefaultIncorrectUse [For demonstration purposes that will be sufficient]
    [ActiveCustomer]
    public Int32? ActiveCustomer { get; set; }
}
 
public class POOrderExt : PXCacheExtension<POOrder>
{
    public abstract class activeCustomer : PX.Data.BQL.BqlInt.Field<activeCustomer> { }
 
    // Acuminator disable once PX1030 PXDefaultIncorrectUse [For demonstration purposes that will be sufficient]
    [ActiveCustomer]
    public Int32? ActiveCustomer { get; set; }
}
 
public class SOOrderExt : PXCacheExtension<SOOrder>
{
    public abstract class activeCustomer : PX.Data.BQL.BqlInt.Field<activeCustomer> { }
 
    // Acuminator disable once PX1030 PXDefaultIncorrectUse [For demonstration purposes that will be sufficient]
    [ActiveCustomer]
    public Int32? ActiveCustomer { get; set; }
}
 As you can see, above our class ActiveCustomer, we've declared bundle of attributes, and then everywhere else, we've used them, but instead of duplication of code, we've re-used them as single line.

How to add GI to side panel and Pivot table to side panel

Hello everybody,

Today a want to share with you approach how to add GI to Side Panel, add Pivot Table to Side Panel, and how current row (current field value) of screen bounds with filter of GI and PT.

Also, I will show how to add all custom features with GI and PT to customization package.

As example, we will add side panel to Customers screen and add two actions, first - custom GI report about sales, second – pivot table with information about Sales Order that connected to current customer on screen.

  1. First, we create custom GI report with joins of tables, result grid with columns that we need for pivot table.

Pivot tables in Acumatica are created and based on GI. All columns in ResultGrid tab will able in pivot table.

 

Add Parameter and Conditions for it.

Click button “VIEW INQUIRY” and check how works result and filter:

Create and setup pivot table as we need:

Click “Save as Pivot”, enter name of pivot table, select check box “Shared Configuration”

Then Acumatica show additional tab with Pivot Table as on screen-shot

Setup Rows, Columns and Values for pivot table, then unclick “Edit pivot table” button  

Pivot table will look as on screen-shot. Also you can drag and drop field between Rows, Columns and Values on pivot table result as you need:

Add side panel to Customer screen (AR303000).

First create new customization package and add AR303000 screen to it.

Save new action in customization and publish customization. Check work side panel with GI on Customers screen:

Add Pivot table to side panel.

Acumatica hides tab of GI with pivot table on side panel and we can see only GI report grid on side panel.

But it is opportunity in Acumatica to add pivot table to side panel using dashboard.

Also we will setup dashboard filter and pipeline it with current customer and pivot table.

  • Create custom dashboard and add parameters as on screen-shot:

Click “VIEW” and setup layout of dashboard

Add pivot table to dashboard: select our custom GI report and Pivot Table (required fields):

Click “FILTER SETTINGS”, add a new and setup it:

Click “FINISH” and exit from DESING mode of dashboard.

Add dashboard with pivot table to side panel.

Open customization package, add new action with side panel type to Customer screen. Select our dashboard ass Destination Screen, also setup Navigation Parameters:

Click OK and Publish customization.

Check how works side panel with Pivot Table. Click “Next” on screen and check how pivot table changes data, depends from current customer:

 

How To Show Tab And Grid Always In Acumatica

 

Hi everybody,

today want to mention following use case:

1. Created Tab or Grid or element in Splitter

2. If View returns zero values

3. Element created at step 1 doesn't appear

How to deal with that?

Set AllowAutoHide to false, and Visible to true and element will not hide automatically.

How To Find Pxprojection Which Has Soorder In The Next Line In Acumatica Source Code

 

Hello everybody,

today I want to speak about very useful feature in Visual Studio.

Sometime you may need some kind of source of inspiration from Acumatica source code. But quite often that source of inspiration have text, which is scattered over multiple lines of code.

For example, you want to find file which has PXProjection text in one line, and word SOOrder in the next line. How to make such a search? Window below appears once you click on Ctrl + Shift + F:

with help of .*\r?\n.* you can make search over multiple files. Take a note of what Visual Studio showed to me in output results once I've clicked on Find All:

and then, you can double click on any of those lines, and make sure, that you found something, that is PXProjection, with SOOrder in some of the next lines:

Summary

With such simple technique you can easily hunt for any lines of code in Acumatica framework, and enhance your search results.

 

 

A Dac Extension Must Include The Publis Static Isactive Method

 

Hello everybody,

today  I want to share one line of code for Acuminator for error message:

PX1016 A DAC extension must include the public static IsActive method with the bool return type. Extensions which are constantly active reduce performance. Suppress the error if you need the DAC extension to be constantly active.

In case if you don't want to suppress Acuminator with a comment, you can do something like this inside of your extension:

public static bool IsActive() => true;

Certainly it is not the most elegant way of doing that, as better way could be usage of some attribute for this purpose, or for example use inheritance, but as of now, the smallest amount of code, you can use that line of code which is presented here. 

 

 

How To Get Key Fields Of Dac Class In Acumatica

 

Hello everybody,

recently for me it was needed to find out all key fields of DAC class. Code below does this:

 

public List<string> GetKeyFieldsOfDAC(Type dacClass)
{
    var result = new List<string>();
    var properties = dacClass.GetProperties().ToList(); 
    
    foreach (PropertyInfo info in properties)
    {
        var attrs = info.GetCustomAttributes(true);
        foreach (object attr in attrs)
        {
            if (attr.HasProperty("IsKey"))
            {
                dynamic typedAttribute = attr;
                if (typedAttribute.IsKey)
                {
                    result.Add(info.Name);
                }
            }
        }
    }
 
    return result;
}

 

And HasProperty method implementation goes below:

 

public static bool HasProperty(this object objectToCheck, string property)
{
    try
    {
        var type = objectToCheck.GetType();
        var prop = type.GetProperty(property);
        if (prop != null)
        {
            return true;
        }
    }
    catch (AmbiguousMatchException// it means we have more then one property
    {
        return true;
    }
 
    return false;
}

 That is not the most elegant solution in my life, and if you want to suggest a better one, please feel free to suggest.