Selecting Data using SQL Functions

  • 1.5K Views
  • Last Post 15 February 2017
DerekMaciak posted this 15 February 2017

SQL has many built-in functions for performing calculations on data.

Useful aggregate functions:

  • AVG() - Returns the average value
  • COUNT() - Returns the number of rows
  • FIRST() - Returns the first value
  • LAST() - Returns the last value
  • MAX() - Returns the largest value
  • MIN() - Returns the smallest value
  • SUM() - Returns the sum

The Accelerator supports this by defining the function expression on the AB_DataSourcePropertyReference class within the DataMaps am_LoadFieldMaps method.

For Example, I have an Order Item file that has a Product ID (YD1I1PID) field and a Unit Price (YD1IPRUN) field. For demonstration purposes, I want to sum the Unit Price for each unique Product ID.  To do this I need to Group_by Product ID and use the SUM function on the Unit Price. 

We first need to add a property to the Order Item Entity that will hold the Total Price value which will contain the SUM of the Unit Price.

[Display(Name = "TOTALPRICE", ResourceType = typeof(DescriptionResource))]
[AB_Length(8)]
[DataMember]
public decimal? TotalPrice //Map Field: YD1IPRUN
  {
      get { return am_GetPropertyValue(TotalPriceProperty); }
      set { am_SetPropertyValue(TotalPriceProperty, value); }
  }
public static AB_PropertyMetadata<decimal?> TotalPriceProperty = am_CreatePropertyMetaData<decimal?>("TotalPrice", DescriptionResource.TOTALPRICE, null);

Next, we need to add a data map which will contain the SQL function and map the result into the TotalPrice property. Add a map as you normally would, but receive the result into a variable. In this case I am receiving the Type AB_DataSourcePropertyReference into the variable "sum".  Then you can set the property ap_FunctionExpression with  the SQL function you want to use. Notice that I also added a data category "YD1I_TotalsByProduct". I only want to select this field and do the sum when using this data catagory. 

var sum = maps.am_AddDataMap("TotalPrice", OrderItemEntity.TotalPriceProperty, dataCategories: new List<string> { "YD1I_TotalsByProduct" });
sum.ap_FunctionExpression = "SUM(YD1IPRUN) as \"TotalPrice\"";

The data category "YD1I_TotalsByProduct" was also added to the Product Internal ID which is the field I want to group by. You will notice that I also have the data category "*ALL" which means this field will always be selected if no category is applied.

maps.am_AddDataMap("YD1I1PID", OrderItemEntity.ProductInternalIDProperty, dataCategories: new List<string> { "*ALL", "YD1I_TotalsByProduct" });

The last step is to add the Group_By and Category.  The Group By will return a single record per Product ID and the Category allows me to select only certain fields (In this case Product Internal ID and Total Price). We can do this on the ap_Query object which is passed with the AB_SelectInputArgs.  I generally like to add the Group By and Category in the am_Select of the Business Process class and condition it based on an Accelerator view. You will also notice that I am setting the ap_RecordsPerRequest and the ap_MaxCount to a max value. When running this query we need to return all records as Next Previous is not supported with this Group_BY.

public override AB_SelectReturnArgs am_Select(AB_SelectInputArgs inputArgs)
{
    if (inputArgs.ap_View == "YD1I_TotalsByProduct")
            {
                inputArgs.ap_Query.am_AddGroupBy(OrderItemDataEntity.OrderItemEntity.ProductInternalIDProperty);
                inputArgs.ap_Category = inputArgs.ap_View;
                inputArgs.ap_RecordsPerRequest  = int.MaxValue;
                inputArgs.ap_MaxCount = int.MaxValue;
            }

        var retArgs = base.am_Select(inputArgs);
        return retArgs;
}

Order By: Standard | Newest | Votes
DerekMaciak posted this 15 February 2017

Here is what the generated SQL looks like:

SELECT YD1I.YD1I1PID as "ProductInternalID", SUM(YD1IPRUN) as "TotalPrice" FROM YD1I  GROUP BY YD1I.YD1I1PID 

DerekMaciak posted this 15 February 2017

If you need to support a SQL Query in the WPF UX that uses a Group_BY, you may want to hide the Preview Pane, Remove  the next/Previous button, and disable all commands such as Open, New, Copy, Delete etc.

Here is an example of adding an Acelerator View to an existing module that Groups the data by Product Internal ID and Sums the Unit Price. 

I create the Accelerator View "YD1I_TotalsByProduct" and added the 2 fields  Product Internal ID (YD1I1PID) and Total Price (TotalPrice).

In the code behind of the Content window of the Order Item module which is where this new view was added, I conditioned on the view so that these rules only apply when that view is selected. I did an override on the method am_BeforeSearchStarted.

protected override void am_BeforeSearchStarted(AB_SelectInputArgs inputArgs, AB_ModuleExplorerViewEntity viewEntity, List<AB_KeyValueHolder> searchParentKeys)
{
    // Set the default Values
    ap_PreviewPaneIsVisible = true;
    ap_HideNextPrevToolbar = false;
    _ViewModel.SetCommandEnabledState(true);
    ap_ShowSearchTotalRecordCount = true;


    if (inputArgs.ap_View == "YD1I_TotalsByProduct")
    {
        // Set the Preview Pane to visible false
        ap_PreviewPaneIsVisible = false;
        // Hide the Next/Previous toolbar as all records need to be returned
        ap_HideNextPrevToolbar = true;
        // Call View module to set commands enabled false
        _ViewModel.SetCommandEnabledState(false);
        // Hide the Search Count total and just show the result total because all records are being returned
        ap_ShowSearchTotalRecordCount = false;
    }

    base.am_BeforeSearchStarted(inputArgs, viewEntity, searchParentKeys);
}

In the am_BeforeProcessCommand, I added a condition to ignore and handle the open and display commands. This prevents the double click and enter keypress from opening the detail record.

protected override void am_BeforeProcessCommand(AB_Command command, RoutedEventArgs e)
{
    // Access the Current Selected Entity
    //var selectedEntity = ap_SelectedEntity as OrderItemEntity;
    //if (selectedEntity != null)
    //{
    //    var myVariable = selectedEntity.<Property>;
    //}


    if (ap_currentView.ViewName == "YD1I_TotalsByProduct")
    {
        if (command.ap_CommandID == AB_CommonCommandIDs.Open || command.ap_CommandID == AB_CommonCommandIDs.Display)
        {
            e.Handled = true;
            return;
        }

    }

    switch (command.ap_CommandID)
    {
        //case "<CommandID>":

        //    Do Something ...

        //    set e.Handled to true to prevent the higher level from executing its command click logic and to prevent further processing by the Detail.
        //    e.Handled = true;

        //    break;

        default:
            break;
    }
}

The last step is to add the method "SetCommandEnabledState" to the view model. You can add any commands necessary that you want to be disabled.

public void SetCommandEnabledState(bool state)
{
    am_SetCommandState(new AB_CommandState("DELETE", true, false, state));
    am_SetCommandState(new AB_CommandState("PERMDELETE", true, false, state));
    am_SetCommandState(new AB_CommandState("OPEN", true, false, state));
    am_SetCommandState(new AB_CommandState("DISPLAY", true, false, state));
    am_SetCommandState(new AB_CommandState("NEW", true, false, state));
    am_SetCommandState(new AB_CommandState("COPY", true, false, state));
    am_SetCommandState(new AB_CommandState("PREVIEW", true, false, state));

}

DerekMaciak posted this 15 February 2017

Prior to Accelerator Version 7.2, you may see the word "of" after the Items Loaded count. You can easily remove this by setting the ap_SummaryContent.ap_SearchTotalRecordCountVisibility property after the search is complete.  This is fixed in version 7.2 and this code behind fix can remain in the code after you upgrade to 7.2. Simply add the override below to your content window code behind.

protected override void am_SearchCompleted()
{
    base.am_SearchCompleted();


    if (!ap_ShowSearchTotalRecordCount)
    {
        ap_SummaryContent.ap_SearchTotalRecordCountVisibility = Visibility.Collapsed;
    }


}

Close