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();
}
