Related to issue 38751: Update M_GETJSONDESCRIPTION with last changes
authorGuillermo Alvarez de Eulate <guillermo.alvarez@openbravo.com>
Thu, 14 Jun 2018 08:54:42 +0200
changeset 34196 b6331343739c
parent 34195 f19a735107f1
child 34197 fc2d94aa22ed
Related to issue 38751: Update M_GETJSONDESCRIPTION with last changes
src-db/database/model/functions/M_GETJSONDESCRIPTION.xml
--- a/src-db/database/model/functions/M_GETJSONDESCRIPTION.xml	Wed Jun 13 19:35:51 2018 +0000
+++ b/src-db/database/model/functions/M_GETJSONDESCRIPTION.xml	Thu Jun 14 08:54:42 2018 +0200
@@ -26,7 +26,7 @@
  * under the License.
  * The Original Code is Openbravo ERP.
  * The Initial Developer of the Original Code is Openbravo SLU
- * All portions are Copyright (C) 2017 Openbravo SLU
+ * All portions are Copyright (C) 2017-2018 Openbravo SLU
  * All Rights Reserved.
  * Contributor(s):  ______________________________________.
  *************************************************************************
@@ -41,6 +41,10 @@
   v_serno VARCHAR2(2000):='';
   v_lot VARCHAR2(2000):='';
   v_guaranteedate VARCHAR2(2000):='';
+  v_escaped_serno VARCHAR2(2000):='';
+  v_escaped_lot VARCHAR2(2000):='';
+  v_escaped_guaranteedate VARCHAR2(2000):='';
+  v_escaped_attvalue VARCHAR2(2000):='';
   v_serno_sk VARCHAR(60):='SerialNoLabel';
   v_guaranteedate_sk VARCHAR(60):='ExpirationDateLabel';
   v_lot_sk VARCHAR(60):='LotLabel';
@@ -48,6 +52,11 @@
   v_guaranteedate_label AD_Message.MsgText%TYPE;
   v_lot_label AD_Message.MsgText%TYPE;
   v_language_code VARCHAR(6);
+  v_att_set_instance_id_to_use VARCHAR(32);
+  v_ref_inv_id VARCHAR(32);
+  v_backslash VARCHAR(1):= CHR(92);
+  v_two_backslash VARCHAR(2);
+  v_backslash_and_quote VARCHAR(2);
   TYPE RECORD IS REF CURSOR;
   Cur_Attribute RECORD;
   
@@ -56,19 +65,6 @@
   select ad_message_get2(v_serno_sk, v_language_code) INTO v_serno_label from dual;
   select ad_message_get2(v_guaranteedate_sk, v_language_code) INTO v_guaranteedate_label from dual;
   select ad_message_get2(v_lot_sk, v_language_code) INTO v_lot_label from dual;
-  
-  FOR Cur_Attribute IN
-    (SELECT mai.m_attribute_id as attributeId, coalesce(to_char(mai.value), '') as attributeValue, ma.name as attributeName, mau.seqno as seqNo, ma.ismandatory as isMandatory
-     FROM m_attributeinstance mai
-     INNER JOIN m_attribute ma on (ma.m_attribute_id=mai.m_attribute_id)
-     INNER JOIN m_attributesetinstance masi on (masi.m_attributesetinstance_id=mai.m_attributesetinstance_id)
-     INNER JOIN m_attributeuse mau on (ma.m_attribute_id = mau.m_attribute_id and masi.m_attributeset_id = mau.m_attributeset_id)
-     WHERE mai.m_attributesetinstance_id = p_attributesetinstance_id
-     ORDER BY seqno asc, ma.name)
-  LOOP
-    v_json:=v_json || '"' || Cur_Attribute.attributeId || '":{"value":"' || Cur_Attribute.attributeValue || '","name":"' || Cur_Attribute.attributeName || '","ismandatory":' || (CASE WHEN (Cur_Attribute.isMandatory = 'Y') THEN 'true' ELSE 'false' END)  || ',"seqno":' || Cur_Attribute.seqNo || '},';
-  END LOOP;
-  
   SELECT coalesce(to_char(case when mas.M_SERNOCTL_ID is null AND p_exclude_characters = 'N' then concat('#', masi.serno) else masi.serno end), ''),
          coalesce(to_char(case when mas.M_LOTCTL_ID is null AND p_exclude_characters = 'N' then concat('L', masi.lot) else masi.lot end),  ''), 
          coalesce(to_char(TO_DATE(masi.guaranteedate), p_dateformatsql), '')
@@ -79,9 +75,50 @@
   INNER JOIN m_attributeset mas on (mas.m_attributeset_id = masi.m_attributeset_id)
   where masi.m_attributesetinstance_id = p_attributesetinstance_id;
 
-  v_json:=v_json || '"lot":{"value":"' || v_lot || '","name":"lot","label":"' || v_lot_label || '"},';
-  v_json:=v_json || '"serialno":{"value":"' || v_serno || '","name":"serialno","label":"' || v_serno_label || '"},';
-  v_json:=v_json || '"guaranteedate":{"value":"' || v_guaranteedate || '","name":"guaranteedate","label":"' || v_guaranteedate_label || '"}';
+  v_two_backslash := v_backslash || v_backslash;
+  v_backslash_and_quote := v_backslash || '"';
+  
+  v_escaped_lot := replace(v_lot, CHR(10), '');
+  v_escaped_lot := replace(v_escaped_lot, CHR(13), '');
+  v_escaped_lot := replace(v_escaped_lot, v_backslash, v_two_backslash);
+  v_escaped_lot := replace(v_escaped_lot, '"', v_backslash_and_quote);
+  
+  v_escaped_serno := replace(v_serno, CHR(10), '');
+  v_escaped_serno := replace(v_escaped_serno, CHR(13), '');
+  v_escaped_serno := replace(v_escaped_serno, v_backslash, v_two_backslash);
+  v_escaped_serno := replace(v_escaped_serno, '"', v_backslash_and_quote);
+  
+  v_escaped_guaranteedate := replace(v_guaranteedate, CHR(10), '');
+  v_escaped_guaranteedate := replace(v_escaped_guaranteedate, CHR(13), '');
+  v_escaped_guaranteedate := replace(v_escaped_guaranteedate, v_backslash, v_two_backslash);
+  v_escaped_guaranteedate := replace(v_escaped_guaranteedate, '"', v_backslash_and_quote);
+  
+  v_json:=v_json || '"lot":{"value":"' || v_escaped_lot || '","name":"lot","label":"' || v_lot_label || '"},';
+  v_json:=v_json || '"serialno":{"value":"' || v_escaped_serno || '","name":"serialno","label":"' || v_serno_label || '"},';
+  v_json:=v_json || '"guaranteedate":{"value":"' || v_escaped_guaranteedate || '","name":"guaranteedate","label":"' || v_guaranteedate_label || '"}';
+
+  SELECT m_refinventory_id, parent_attributesetinstance_id
+  INTO v_ref_inv_id, v_att_set_instance_id_to_use
+  FROM m_attributesetinstance
+  WHERE m_attributesetinstance_id = p_attributesetinstance_id;
+  
+  IF (v_ref_inv_id IS NULL) THEN
+    v_att_set_instance_id_to_use:=p_attributesetinstance_id;
+  END IF;
+  
+  FOR Cur_Attribute IN
+    (SELECT mai.m_attribute_id as attributeId, coalesce(to_char(mai.value), '') as attributeValue, ma.name as attributeName
+     FROM m_attributeinstance mai
+     INNER JOIN m_attribute ma on (ma.m_attribute_id=mai.m_attribute_id)
+     WHERE mai.m_attributesetinstance_id = v_att_set_instance_id_to_use
+     ORDER BY ma.name)
+  LOOP
+    v_escaped_attvalue := replace(Cur_Attribute.attributeValue, CHR(10), '');
+    v_escaped_attvalue := replace(v_escaped_attvalue, CHR(13), '');
+    v_escaped_attvalue := replace(v_escaped_attvalue, v_backslash, v_two_backslash);
+    v_escaped_attvalue := replace(v_escaped_attvalue, '"', v_backslash_and_quote);
+    v_json:=v_json || ',"' || Cur_Attribute.attributeId || '":{"value":"' || v_escaped_attvalue || '","name":"' || Cur_Attribute.attributeName || '"}';
+  END LOOP;
 
   v_json:=v_json || '}';