EMUG May 2022 - Statistical Analysis BAQ and how to Update an External BAQ Data Table

Hey All
Attached is the BAQ that will Calculate the IQR OUTLIER for a Sales Order
MarginCostStatChecker.baq (48.4 KB)

This BAQ Calculates Outlier based on Interquartile Range and allows you to present this information to a user like this

Here’s how to call the above BAQ from a BPM

  using (var dq = Ice.Assemblies.ServiceRenderer.GetService<Ice.Contracts.DynamicQuerySvcContract>(Db))
  {
    var dqds = dq.GetQueryExecutionParametersByID("MarginCostStatChecker");
    var partNumParam = dqds.ExecutionParameter.Where(p=>p.ParameterID=="PartNum").FirstOrDefault();
    partNumParam.ParameterValue = partNum;
    partNumParam.IsEmpty=false;
    partNumParam.SetRowState(IceRowState.Updated);
    
    var custNumParam = dqds.ExecutionParameter.Where(p=>p.ParameterID=="CustNum").FirstOrDefault();
    custNumParam.ParameterValue = custNum.ToString();
    custNumParam.IsEmpty=false;
    custNumParam.SetRowState(IceRowState.Updated);
    
    
    var lineValueParam = dqds.ExecutionParameter.Where(p=>p.ParameterID=="LineValue").FirstOrDefault();
    lineValueParam.ParameterValue = lineValue.ToString();
    lineValueParam.IsEmpty=false;
    lineValueParam.SetRowState(IceRowState.Updated);
    
    var lineCostParam = dqds.ExecutionParameter.Where(p=>p.ParameterID=="LineCost").FirstOrDefault();
    lineCostParam.ParameterValue = lineCost.ToString();
    lineCostParam.IsEmpty=false;
    lineCostParam.SetRowState(IceRowState.Updated);
    
    var orderLineParam = dqds.ExecutionParameter.Where(p=>p.ParameterID=="OrderLine").FirstOrDefault();
    orderLineParam.ParameterValue = orderLine.ToString();
    orderLineParam.IsEmpty=false;
    orderLineParam.SetRowState(IceRowState.Updated);
    
    var orderNumParam = dqds.ExecutionParameter.Where(p=>p.ParameterID=="OrderNum").FirstOrDefault();
    orderNumParam.ParameterValue = orderNum.ToString();
    orderNumParam.IsEmpty=false;
    orderNumParam.SetRowState(IceRowState.Updated);
    
    bool more=false;
    var resultDs = dq.ExecuteByID("MarginCostStatChecker", dqds);
  }

Updating External BAQ Table using SQL Comand

string conn = "";

// The connection string is encrypted at Rest so we need to decrypt it before we can use it.
using(System.Security.Cryptography.SymmetricAlgorithm symmetricAlgorithm = Epicor.Security.Cryptography.SymmetricAlgorithmFactory.Create(Epicor.Security.Cryptography.SymmetricCryptoAlgorithm.Aes)) {

    var extConnStr = (from i in Db.BAQExtDatasource where i.DatasourceName == "Azure-Replication"
    select i.ConnectionString).FirstOrDefault();

    conn = Ice.Lib.Bpm.Helpers.TextEncryptionHelper.Decrypt(Convert.FromBase64String(extConnStr), symmetricAlgorithm); //Decrypt Connection String.
}

// Run the SQL Update.
using(SqlConnection sqlConn = new SqlConnection(conn)) 
{
  sqlConn.Open();
  using(SqlCommand _cmd = sqlConn.CreateCommand()) 
  {
    
      foreach(var reslt in this.queryResultDatasetHolder.Original.Results.Where(r => r.Updated())) 
      {
        try
        {
          _cmd.CommandText = $"Update dbo.OrderHed Set PONum = {reslt.OrderHed_PONum} where SysRowID ='{reslt.OrderHed_SysRowID}'";
          _cmd.ExecuteNonQuery();
        }
        catch(Exception ex)
        {
          reslt.Calculated_ConnString = ex.Message;
        }
      }
   
  }
  sqlConn.Close();
}
2 Likes