Convert SQL code to C# code for optimizer rule on A/P invoice

ThijsG

I created an optimizer rule to check for duplicated BP reference numbers on A/P invoices.


This rule checks A/P invoice draft documents on BP reference numbers when you want to add a new A/P invoice.


 


It seems like the code is correct but it the function doesn't work.


As it is not possible to debug the SQL code I need to convert it to cSharp as I will be able to put some message boxes in.


As I am not familiar with cSharp is there anyone who would be able to convert my Optimer SQL code to cSharp so I can see what is missing in my rule. Below the code.


Thanks!


 


DECLARE @CardCode AS NVARCHAR(8), 

@Ref AS NVARCHAR(100) 

SET @CardCode = '[%G;4]'

SET @Ref = '[%G;14]'

select case when (SELECT COUNT (T0.DocEntry) FROM ODRF T0 where T0.ObjType = 18 and T0.CardCode = @CardCode and 

T0.NumAtCard = @Ref and T0.DocStatus = 'O') = 0

then NULL

   ELSE 1

END

Michael Hubenschmid

Hello,


 


this wouild be the code:



try
{
StatusBar.WriteWarning("DEBUG - Rule: " + pVal.RuleInfo.RuleName + " was triggered.");
// Your Code

string cardCode = TextEdit.GetFromUID(pVal.Form, "4").Value.ToString();
string refNo = TextEdit.GetFromUID(pVal.Form, "14").Value.ToString();

string checkRefNoQry = @"SELECT COUNT (T0.DocEntry) as 'CountRefNo' FROM ODRF T0 where T0.ObjType = 18 and T0.CardCode = '" + cardCode + "' and T0.NumAtCard = '" + refNo + "' and T0.DocStatus = 'O'";

using(System.Data.SqlClient.SqlDataReader sdrCheckRefNo = SwissAddonFramework.B1Connector.GetB1Connector().ExecuteQuery(checkRefNoQry))
{
if(sdrCheckRefNo.Read())
{
int countRefNo = int.Parse(sdrCheckRefNo["CountRefNo"].ToString());

if(countRefNo > 0)
{
MessageBox.Show("Ref.Number still exists!", "OK");
return false;
}
}
}
}
catch (Exception ex)
{
string errorMessage = string.Format("Error in {0} Rule '{1}': {2}", pVal.RuleInfo.RuleType, pVal.RuleInfo.RuleName, ex.Message);
MessageBox.Show(errorMessage, "OK");
StatusBar.WriteError(errorMessage);
Debug.WriteMessage(errorMessage, Debug.DebugLevel.Exception);
}

return true;

Kind regards,


 


Michael


ThijsG

Thanks you very much Michael.


The applied code in C# works fine!


Regards Thijs


0

Comments

0 comments

Please sign in to leave a comment.

Didn't find what you were looking for?

New post