How do I best messages when SQL is complex?

[Rinaldo]

I have a problem recurrent with messages to users, when I have complex SQL that is dependent of many parameters.

I don't know if exist in idempiere a solution to this when the developer is making something this.

Suppose that there are four parameters not null in a SQL with zero lines of return.

Tradicional messages is: "There is no date for this.".

But this isn't  sufficient to help the user.

The good it would be something how: "Nothing was found in tax definition because the following statements are false: document type [= Ar Invoice (C_DocType = 116)] and target region [= CT (To_CRegion_ID = 102)], when proposed as true which category of the partner [= Simple (= 1000009)] and product category [= Dog (10000050)]."

I am thinking in to read the string "where", separate the outermost expressions (AND) and to make the message (for any sql).

But before, have a solution on the Idempiere to this?

Does anyone else have this kind of difficulty with SQL?

A exemple (LBR Brazilian plugin):

String where = "IsActive='Y' AND AD_Org_ID IN (0, ?) ";

where += "AND (C_BPartner_ID IS NULL OR C_BPartner_ID=?) ";

where += "AND (C_DocType_ID IS NULL OR C_DocType_ID=?) ";  //116

where += "AND (C_Region_ID IS NULL OR C_Region_ID=?) ";

where += "AND (To_Region_ID IS NULL OR To_Region_ID=?) ";  //102

where += "AND (LBR_BPartnerCategory_ID IS NULL OR LBR_BPartnerCategory_ID=?) "; //1000009

where += "AND (LBR_FiscalGroup_BPartner_ID IS NULL OR LBR_FiscalGroup_BPartner_ID=?) ";

where += "AND (LBR_FiscalGroup_Product_ID IS NULL OR LBR_FiscalGroup_Product_ID=?) ";

where += "AND (LBR_NCM_ID IS NULL OR LBR_NCM_ID=?) ";

where += "AND (LBR_ProductCategory_ID IS NULL OR LBR_ProductCategory_ID=?) "; //10000050

where += "AND (M_Product_ID IS NULL OR M_Product_ID=?) ";

where += "AND lbr_IsSubTributaria IN ('B', ?) ";

where += "AND IsSOTrx IN ('B', ?) ";

where += "AND (lbr_TransactionType IS NULL OR lbr_TransactionType=?) ";

List<MLBRTaxDefinition> list = new Query (Env.getCtx(), MLBRTaxDefinition.Table_Name, where, null)

.setParameters(new Object[]{AD_Org_ID, C_BPartner_ID, C_DocType_ID, C_Region_ID, To_Region_ID, 

LBR_BPartnerCategory_ID, LBR_FiscalGroup_BPartner_ID, LBR_FiscalGroup_Product_ID, LBR_NCM_ID, 

LBR_ProductCategory_ID, M_Product_ID, (lbr_IsSubTributaria ? "Y" : "N"), (isSOTrx ? "Y" : "N"), lbr_TransactionType});

//if (list.size()==0) {  

//      System.out.println("There is no date for this.");  // common solution

//}

[Ricardo Alexsander Santana]

Hi Rinaldo,

If I understand you correctly, I don't think there is a generic solution to this. You should implement your own way to warn the user that his search criteria is too specific and did returned any records.

In terms of Localization Brazil, you should consider creating generics "Tax Definition" configurations and not only BPartner specific.

Regards,

--
Ricardo Alexsander Santana
ralex...@gmail.com

--
You received this message because you are subscribed to the Google Groups "iDempiere" group.
To unsubscribe from this group and stop receiving emails from it, send an email to idempiere+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/idempiere/0b90d371-fc4b-489f-b61e-9cade19e0465%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Rinaldo]

I understand the answer about more generic tax settings to LBR.

I think I'm going to implement a generic solution, since I have some experience with parser of instructions SQL.

I asked this here, because maybe it is interesting something for everyone in the Idempiere, independent of the plugin. 

Even though, thank you very much for your attention.

To unsubscribe from this group and stop receiving emails from it, send an email to idempiere+...@googlegroups.com.

[Rinaldo]

This is how I imagine (draft)

First: I count the more external AND (here is 15 and plus 1), I have some experience with lib that it does this.

Second: Loop over string "where" { 16 ocorr } to it make many inserts in a log. (This could to be use to query the facts after) .

(1)

SELECT Distinct 1

FROM lbr_taxdefinition

 WHERE IsActive='Y';

>return 1; 

boolean result=(return==0)?false:true;

INSERT into lbr_restriction_log(trxName, "IsActive='Y'", "fixed", result);  //witout param

(2)

SELECT Distinct 1

FROM lbr_taxdefinition

WHERE IsActive='Y'

  AND AD_Org_ID IN (0, ?);

>return 1

INSERT into lbr_restriction_log(trxName, "AD_Org_ID IN (0, ?)", param?1, result);

(3)

SELECT Distinct 1

FROM lbr_taxdefinition

WHERE IsActive='Y'

  AND AD_Org_ID IN (0, ?);

  AND (C_BPartner_ID IS NULL OR C_BPartner_ID=?);

>return 1  

boolean result=(return==0)?false:true;

INSERT into lbr_restriction_log(trxName, "(C_BPartner_ID IS NULL OR C_BPartner_ID=?)", param?1, result);

(4) --> false

SELECT Distinct 1

FROM lbr_taxdefinition

WHERE IsActive='Y'

  AND AD_Org_ID IN (0, ?);

  AND (C_BPartner_ID IS NULL OR C_BPartner_ID=?);

  AND (C_DocType_ID IS NULL OR C_DocType_ID=? );

>return 0;

boolean result=(return==0)?false:true;

INSERT into lbr_restriction_log(trxName, "C_DocType_ID IS NULL OR C_DocType_ID=? );", param?1, result);

(5)

SELECT Distinct 1

FROM lbr_taxdefinition

WHERE IsActive='Y'

  AND AD_Org_ID IN (0, ?);

  AND (C_BPartner_ID IS NULL OR C_BPartner_ID=?)

//  AND (C_DocType_ID IS NULL OR C_DocType_ID=? )

  AND (C_Region_ID IS NULL OR C_Region_ID=?);

>return 1; 

boolean result=(return==0)?false:true;

INSERT into lbr_restriction_log(trxName, "(C_Region_ID IS NULL OR C_Region_ID=?)", param?1, result);

(6) --> false

SELECT Distinct 1

FROM lbr_taxdefinition

WHERE IsActive='Y'

  AND AD_Org_ID IN (0, ?);

  AND (C_BPartner_ID IS NULL OR C_BPartner_ID=?)

//  AND (C_DocType_ID IS NULL OR C_DocType_ID=? )

  AND (C_Region_ID IS NULL OR C_Region_ID=?)

  AND (To_Region_ID IS NULL OR To_Region_ID=?);

>return 0; 

boolean result=(return==0)?false:true;

INSERT into lbr_restriction_log(trxName, "(C_Region_ID IS NULL OR C_Region_ID=?)", param?1, result);

(7) 

SELECT Distinct 1

FROM lbr_taxdefinition

WHERE IsActive='Y'

  AND AD_Org_ID IN (0, ?);

  AND (C_BPartner_ID IS NULL OR C_BPartner_ID=?)

//  AND (C_DocType_ID IS NULL OR C_DocType_ID=? ) //116

  AND (C_Region_ID IS NULL OR C_Region_ID=?)

//  AND (To_Region_ID IS NULL OR To_Region_ID=?); //102

...

(8)

    AND (LBR_BPartnerCategory_ID IS NULL OR LBR_BPartnerCategory_ID=?) //1000009

>return 1

boolean result=(return==0)?false:true;

INSERT into lbr_restriction_log(trxName, "(LBR_BPartnerCategory_ID IS NULL OR LBR_BPartnerCategory_ID=?)", param?1, result);

(9)

    AND (LBR_FiscalGroup_BPartner_ID IS NULL OR LBR_FiscalGroup_BPartner_ID=?) //

>return 1; ...

(10)

    AND (LBR_FiscalGroup_Product_ID IS NULL OR LBR_FiscalGroup_Product_ID=?) 

>return 1; ...

(11)

    AND (LBR_NCM_ID IS NULL OR LBR_NCM_ID=?) 

>return 1; ...

(12)

    AND (LBR_ProductCategory_ID IS NULL OR LBR_ProductCategory_ID=?)  //10000050

>return 1; ...

boolean result=(return==0)?false:true;

INSERT into lbr_restriction_log(trxName, "(LBR_BPartnerCategory_ID IS NULL OR LBR_BPartnerCategory_ID=?)", param?1, result);

(13)

    AND (M_Product_ID IS NULL OR M_Product_ID=?) 

>return 1; ...

(14)

    AND lbr_IsSubTributaria IN ('B', ?) 

>return 1; ...

(15)

    AND IsSOTrx IN ('B', ?) 

>return 1; ...

(16)

    AND (lbr_TransactionType IS NULL OR lbr_TransactionType=?) 

>return 1; ...

Third: To make the Array with the log

Array:

Tabel	Name Table	Name Column Source Table	DataName	Condition	Find out value	Test
C_DocType	Document Type	C_DocType_ID	with data	=	Empty	false
C_DocType	Document Type	C_DocType_ID	AR Invoice	=	116	false
C_Region	Region	To_Region_ID	with data	=	Empty	false
C_Region	Region	To_Region_ID	CT	=	102	false
LBR_BPartnerCategory	Category of Partner	LBR_BPartnerCategory_ID	Simples	=	1000009	true
LBR_BproductCategory	Category of Product	LBR_BproducCategory_ID	Dog	=	1000050	true

Fourth:It makes the menssage with the translation.

[Rinaldo]

In the moment I will to insert or update  the table ad_userquery a record named of "Last search applied" to the table (any). It will get conditions of the command SQL and make the text   (code). It is more easy and simple with the parser SQL.

[Rinaldo]

I advanced with my parser SQL to generate the field "code" automatically. (varchar 2000 in ad_userquery, 2000 can to be short in the future) 

I put a example of the result  after this text. When I to advance more I publish the code and a link public in the bitbucket.

Now I have a problem with the IN and with complex querys with subselect. At the time, I want to improve the parser (or advanced search) with the IN.  I looked the symbols types ^=, ~= and others but I think that there isn't nothing to the IN.

I think that if exist it should show the field to mark which ones what user wants (Checkbox with Select and multiple - JS -Ajax). 

Anyone know anything about this? (Before I begin and I to do a new customization that maybe isn't necessary in the advanced search).

Ps.: My idea in the momment  it is add a option in the menu preference: "register querys with zero rows of return". If it is active, 

then I will sugery a change the the program Query.java to execute the parser and after it save in ad_userquery, if anyone of the community will have interest in this improvment.

Example:

public String getCode(String Sql ) 

  // TODO: IN

  // TODO: new Object[]{AD_Org_ID, C_BPartner_ID, C_DocType_ID, C_Region_ID, To_Region_ID,  LBR_BPartnerCategory_ID, LBR_FiscalGroup_BPartner_ID, LBR_FiscalGroup_Product_ID, LBR_NCM_ID,    LBR_ProductCategory_ID, M_Product_ID, (lbr_IsSubTributaria ? "Y" : "N"), (isSOTrx ? "Y" : "N"), lbr_TransactionType}

  // TODO: Sublect  

INPUT:

SELECT * from LBR_TAXDEFINITION 

WHERE (((IsActive = 'Y') 

         OR (AD_Org_ID IN (0, ?))) 

AND ((C_BPartner_ID IS NULL) 

     OR (C_BPartner_ID = ?))

AND ((C_DocType_ID IS NULL) 

OR (C_DocType_ID = ?)) 

AND ((C_Region_ID IS NULL) 

OR (C_Region_ID = ?)) 

AND ((To_Region_ID IS NULL) 

OR (To_Region_ID = ?)) 

AND (LBR_BPartnerCategory_ID BETWEEN 0 AND 3) 

AND ((LBR_FiscalGroup_BPartner_ID IS NULL) 

OR (LBR_FiscalGroup_BPartner_ID = ?)) 

AND ((LBR_FiscalGroup_Product_ID IS NULL) 

OR (LBR_FiscalGroup_Product_ID = ?)) 

AND ((LBR_NCM_ID IS NULL) 

OR (LBR_NCM_ID = ?)) 

AND ((LBR_ProductCategory_ID IS NULL) 

OR (LBR_ProductCategory_ID = ?)) 

AND ((M_Product_ID IS NULL) 

OR (M_Product_ID = ?)) 

AND (lbr_IsSubTributaria IN ('B', ?)) 

AND (IsSOTrx IN ('B', ?)) 

AND ((lbr_TransactionType IS NULL) 

OR (lbr_TransactionType = ?)))

OUTPUT:

IsActive<^> = <^>Y<^><^><^>(((<^>)

<~>AD_Org_ID<^> IN <^>0<^>?<^>OR<^>(<^>))

<~>C_BPartner_ID<^> IS NULL <^><^><^>AND<^>((<^>)

<~>C_BPartner_ID<^> = <^><^>?<^>OR<^>(<^>))

<~>C_DocType_ID<^> IS NULL <^><^><^>AND<^>((<^>)

<~>C_DocType_ID<^> = <^><^>?<^>OR<^>(<^>))

<~>C_Region_ID<^> IS NULL <^><^><^>AND<^>((<^>)

<~>C_Region_ID<^> = <^><^>?<^>OR<^>(<^>))

<~>To_Region_ID<^> IS NULL <^><^><^>AND<^>((<^>)

<~>To_Region_ID<^> = <^><^>?<^>OR<^>(<^>))

<~>LBR_BPartnerCategory_ID<^> BETWEEN <^>0<^>3<^>AND<^>(<^>)

<~>LBR_FiscalGroup_BPartner_ID<^> IS NULL <^><^><^>AND<^>((<^>)

<~>LBR_FiscalGroup_BPartner_ID<^> = <^><^>?<^>OR<^>(<^>))

<~>LBR_FiscalGroup_Product_ID<^> IS NULL <^><^><^>AND<^>((<^>)

<~>LBR_FiscalGroup_Product_ID<^> = <^><^>?<^>OR<^>(<^>))

<~>LBR_NCM_ID<^> IS NULL <^><^><^>AND<^>((<^>)

<~>LBR_NCM_ID<^> = <^><^>?<^>OR<^>(<^>))

<~>LBR_ProductCategory_ID<^> IS NULL <^><^><^>AND<^>((<^>)

<~>LBR_ProductCategory_ID<^> = <^><^>?<^>OR<^>(<^>))

<~>M_Product_ID<^> IS NULL <^><^><^>AND<^>((<^>)

<~>M_Product_ID<^> = <^><^>?<^>OR<^>(<^>))

<~>lbr_IsSubTributaria<^> IN <^>B<^>?<^>AND<^>(<^>)

<~>IsSOTrx<^> IN <^>B<^>?<^>AND<^>(<^>)

<~>lbr_TransactionType<^> IS NULL <^><^><^>AND<^>((<^>)

<~>lbr_TransactionType<^> = <^><^>?<^>OR<^>(<^>)))

[Rinaldo]

Hi, I want to end this post now. publishing my solution.

https://bitbucket.org/Riddrk6/idempiere.lastsql/src/default/src/org/idempiere/lastsql/ParserQuery.java

This is an application to create ad_userquery (advanced search) having a SQL from a table only. (I have not figured out how to do an event for the "Select" in Idempiere)

Ps: My test, I put a row in Query.java to call directly and it worked for many SQL.

I'm putting a this in each code that I think needs to pass more information to the user on what's going on in the application.

I hope more people feel the need to aggregate this in Idempiere, maybe a new method for Query.java