src/org/openbravo/erpCommon/utility/Menu_data.xsql
author Iván Perdomo <ivan.perdomo@openbravo.com>
Mon, 21 Apr 2008 19:06:47 +0000
changeset 712 aa0c40278c5a
parent 423 ecf368072c48
child 799 fef2c5e2feb7
permissions -rw-r--r--
Merged changes from ajaxgrids branch.
<?xml version="1.0" encoding="UTF-8" ?>
<!--
 *************************************************************************
 * The contents of this file are subject to the Openbravo  Public  License
 * Version  1.0  (the  "License"),  being   the  Mozilla   Public  License
 * Version 1.1  with a permitted attribution clause; you may not  use this
 * file except in compliance with the License. You  may  obtain  a copy of
 * the License at http://www.openbravo.com/legal/license.html 
 * Software distributed under the License  is  distributed  on  an "AS IS"
 * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
 * License for the specific  language  governing  rights  and  limitations
 * under the License. 
 * The Original Code is Openbravo ERP. 
 * The Initial Developer of the Original Code is Openbravo SL 
 * All portions are Copyright (C) 2001-2006 Openbravo SL 
 * All Rights Reserved. 
 * Contributor(s):  ______________________________________.
 ************************************************************************
-->





<SqlClass name="MenuData" package="org.openbravo.erpCommon.utility">
   <SqlClassComment></SqlClassComment>
   <SqlMethod name="select" type="preparedStatement" return="multiple">
      <SqlMethodComment></SqlMethodComment>
      <Sql>
        SELECT DISTINCT tn.Node_ID,tn.Parent_ID,tn.SeqNo,menuData.NAME,menuData.Description,menuData.IsSummary,
        menuData.Action, (case menuData.Action when 'W' then MOW.CLASSNAME 
                                              when  'X' then MOX.CLASSNAME
                                              when  'T' then MOT.CLASSNAME
                                              when 'F' then MOF.CLASSNAME
                                              when 'B' then MOB.CLASSNAME
                                              else MOP.CLASSNAME
                                              end) AS CLASSNAME, 
        (case menuData.Action when 'W' then MOMW.MAPPINGNAME
                              when 'X' then MOMX.MAPPINGNAME
                              when 'T' then MOMT.MAPPINGNAME
                              when 'F' then MOMF.MAPPINGNAME
                              when 'B' then MOMB.MAPPINGNAME
                              else MOMP.MAPPINGNAME
                              end) AS MAPPINGNAME, 
        menuData.AD_Process_ID, 
        menuData.AD_Task_ID, 
        menuData.AD_Workflow_ID, 
        menuData.url 
        FROM AD_TREENODE tn,
        (SELECT m.AD_Menu_ID, COALESCE(m_t.NAME, m.NAME) AS NAME, COALESCE(m_t.DESCRIPTION, m.Description) AS DESCRIPTION, 
        m.IsSummary,m.Action, m.AD_Window_ID, m.AD_Process_ID, 
        m.AD_FORM_ID, m.AD_Task_ID, m.AD_Workflow_ID, m.AD_WORKBENCH_ID, m.url 
        FROM AD_MENU m left join AD_MENU_TRL m_t on m.AD_MENU_ID = m_t.AD_MENU_ID 
                                                AND m_t.AD_LANGUAGE = ? 
        WHERE m.IsActive='Y' 
        AND (m.IsSummary='Y' OR m.Action='B' OR m.Action='L' OR m.Action='I'
        OR EXISTS (SELECT * FROM AD_WINDOW_ACCESS wa WHERE wa.AD_Window_ID=m.AD_Window_ID AND wa.AD_Role_ID = TO_NUMBER(?))
        OR EXISTS (SELECT * FROM AD_PROCESS_ACCESS wa WHERE wa.AD_Process_ID=m.AD_Process_ID AND wa.AD_Role_ID = TO_NUMBER(?))
        OR EXISTS (SELECT * FROM AD_FORM_ACCESS wa WHERE wa.AD_Form_ID=m.AD_Form_ID AND wa.AD_Role_ID = TO_NUMBER(?))
        OR EXISTS (SELECT * FROM AD_TASK_ACCESS wa WHERE wa.AD_Task_ID=m.AD_Task_ID AND wa.AD_Role_ID = TO_NUMBER(?))
        OR EXISTS (SELECT * FROM AD_WORKFLOW w, AD_WORKFLOW_ACCESS wa WHERE w.AD_Workflow_ID=wa.AD_Workflow_ID 
                    AND wa.AD_Workflow_ID=m.AD_Workflow_ID 
                    AND w.isActive = 'Y'
                    AND wa.AD_Role_ID = TO_NUMBER(?))
        )) menuData left join AD_WINDOW W on menuData.AD_Window_ID = W.AD_Window_ID
                    left join AD_TAB T on W.AD_Window_ID = T.AD_WINDOW_ID
                    left join AD_MODEL_OBJECT MOW on T.AD_TAB_ID = MOW.AD_TAB_ID
                                                  AND MOW.ACTION    = 'W' 
                                                  AND MOW.ISACTIVE = 'Y'
                                                  AND MOW.ISDEFAULT= 'Y'
                    left join AD_MODEL_OBJECT_MAPPING MOMW on MOW.AD_MODEL_OBJECT_ID = MOMW.AD_MODEL_OBJECT_ID 
                                                  AND MOMW.ISACTIVE  = 'Y'
                                                  AND MOMW.ISDEFAULT = 'Y'
                    left join AD_MODEL_OBJECT MOX on menuData.AD_FORM_ID = MOX.AD_FORM_ID 
                                                  AND MOX.ACTION   = 'X' 
                                                  AND MOX.ISACTIVE = 'Y'
                                                  AND MOX.ISDEFAULT= 'Y'
                    left join AD_MODEL_OBJECT_MAPPING MOMX on MOX.AD_MODEL_OBJECT_ID = MOMX.AD_MODEL_OBJECT_ID 
                                                  AND MOMX.ISACTIVE  = 'Y'
                                                  AND MOMX.ISDEFAULT = 'Y'
                    left join AD_MODEL_OBJECT MOT on menuData.AD_TASK_ID = MOT.AD_TASK_ID 
                                                  AND MOT.ACTION    = 'T' 
                                                  AND MOT.ISACTIVE  = 'Y'
                                                  AND MOT.ISDEFAULT = 'Y'
                    left join AD_MODEL_OBJECT_MAPPING MOMT on MOT.AD_MODEL_OBJECT_ID = MOMT.AD_MODEL_OBJECT_ID 
                                                  AND MOMT.ISACTIVE  = 'Y'
                                                  AND MOMT.ISDEFAULT = 'Y'
                    left join AD_MODEL_OBJECT MOF on menuData.AD_WORKFLOW_ID = MOF.AD_WORKFLOW_ID 
                                                  AND MOF.ACTION   = 'X' 
                                                  AND MOF.ISACTIVE = 'Y'
                                                  AND MOF.ISDEFAULT= 'Y'
                    left join AD_MODEL_OBJECT_MAPPING MOMF on MOF.AD_MODEL_OBJECT_ID = MOMF.AD_MODEL_OBJECT_ID
                                                  AND MOMF.ISACTIVE  = 'Y'
                                                  AND MOMF.ISDEFAULT = 'Y'
                    left join AD_MODEL_OBJECT MOP on menuData.AD_PROCESS_ID = MOP.AD_PROCESS_ID 
                                                  AND MOP.ACTION IN ('P', 'R') 
                                                  AND MOP.ISACTIVE = 'Y'
                                                  AND MOP.ISDEFAULT= 'Y'
                    left join AD_MODEL_OBJECT_MAPPING MOMP on MOP.AD_MODEL_OBJECT_ID = MOMP.AD_MODEL_OBJECT_ID
                                                  AND MOMP.ISACTIVE  = 'Y'
                                                  AND MOMP.ISDEFAULT = 'Y'
                    left join AD_MODEL_OBJECT MOB on menuData.AD_WORKBENCH_ID = MOB.AD_WORKBENCH_ID 
                                                  AND MOB.ACTION   = 'B' 
                                                  AND MOB.ISACTIVE = 'Y'
                                                  AND MOB.ISDEFAULT= 'Y'
                    left join AD_MODEL_OBJECT_MAPPING MOMB on MOB.AD_MODEL_OBJECT_ID = MOMB.AD_MODEL_OBJECT_ID 
                                                  AND MOMB.ISACTIVE  = 'Y'
                                                  AND MOMB.ISDEFAULT = 'Y'
        WHERE tn.node_id = menuData.ad_menu_id
        AND (EXISTS(SELECT 1 FROM AD_TAB 
        WHERE AD_TAB.isactive ='Y'
        GROUP BY AD_TAB.ad_window_id
        HAVING AD_TAB.ad_window_id=T.ad_window_id 
        AND MIN(AD_TAB.seqno)=T.seqno)
        OR T.ad_tab_id is null)
        AND tn.ad_tree_ID = TO_NUMBER(?)
        AND tn.IsActive='Y'
        ORDER BY PARENT_ID, SEQNO
      </Sql>
        <Parameter name="language"/>
        <Parameter name="roleId"/>
        <Parameter name="roleId"/>
        <Parameter name="roleId"/>
        <Parameter name="roleId"/>
        <Parameter name="roleId"/>
        <Parameter name="treeId"/>
   </SqlMethod>

   <SqlMethod name="selectData" type="preparedStatement" return="multiple">
      <SqlMethodComment></SqlMethodComment>
      <Sql>
        SELECT DISTINCT tn.Node_ID,tn.Parent_ID,tn.SeqNo,menuData.NAME,menuData.Description,menuData.IsSummary,
        menuData.Action, (case menuData.Action when 'W' then MOW.CLASSNAME
                                        when 'X' then MOX.CLASSNAME
                                        when 'T' then MOT.CLASSNAME
                                        when 'F' then MOF.CLASSNAME
                                        when 'B' then MOB.CLASSNAME
                                        else MOP.CLASSNAME
                                        end) AS CLASSNAME, 
        (case menuData.Action when 'W' then MOMW.MAPPINGNAME
                            when 'X' then MOMX.MAPPINGNAME
                            when 'T' then MOMT.MAPPINGNAME
                            when 'F' then MOMF.MAPPINGNAME
                            when 'B' then MOMB.MAPPINGNAME
                            else MOMP.MAPPINGNAME
                            end) AS MAPPINGNAME, 
        menuData.AD_Process_ID, 
        menuData.AD_Task_ID, 
        menuData.AD_Workflow_ID, menuData.url 
        FROM AD_TREENODE tn,
        (SELECT m.AD_Menu_ID, COALESCE(m_t.NAME, m.NAME) AS NAME, COALESCE(m_t.DESCRIPTION, m.Description) AS DESCRIPTION, 
                m.IsSummary,m.Action, m.AD_Window_ID, m.AD_Process_ID, 
                m.AD_FORM_ID, m.AD_Task_ID, m.AD_Workflow_ID, m.AD_WORKBENCH_ID, m.url 
           FROM AD_MENU m left join AD_MENU_TRL m_t on m.AD_MENU_ID = m_t.AD_MENU_ID  
                                                   AND m_t.AD_LANGUAGE = ?
          WHERE m.IsActive='Y' 
        ) menuData left join AD_WINDOW W on menuData.AD_Window_ID = W.AD_Window_ID
                   left join AD_TAB T on W.AD_Window_ID = T.AD_WINDOW_ID
                   left join AD_MODEL_OBJECT MOW on T.AD_TAB_ID = MOW.AD_TAB_ID  
                                                AND MOW.ACTION    = 'W' 
                                                AND MOW.ISACTIVE  = 'Y'
                                                AND MOW.ISDEFAULT = 'Y'
                   left join AD_MODEL_OBJECT_MAPPING MOMW on MOW.AD_MODEL_OBJECT_ID = MOMW.AD_MODEL_OBJECT_ID 
                                                AND MOMW.ISACTIVE  = 'Y'
                                                AND MOMW.ISDEFAULT = 'Y'
                   left join AD_MODEL_OBJECT MOX on menuData.AD_FORM_ID = MOX.AD_FORM_ID
                                                AND MOX.ACTION   = 'X' 
                                                AND MOX.ISACTIVE = 'Y'
                                                AND MOX.ISDEFAULT= 'Y'
                   left join AD_MODEL_OBJECT_MAPPING MOMX on MOX.AD_MODEL_OBJECT_ID = MOMX.AD_MODEL_OBJECT_ID 
                                                AND MOMX.ISACTIVE = 'Y'
                                                AND MOMX.ISDEFAULT= 'Y'
                   left join AD_MODEL_OBJECT MOT on menuData.AD_TASK_ID = MOT.AD_TASK_ID 
                                                AND MOT.ACTION   = 'T' 
                                                AND MOT.ISACTIVE = 'Y'
                                                AND MOT.ISDEFAULT= 'Y'
                   left join AD_MODEL_OBJECT_MAPPING MOMT on MOT.AD_MODEL_OBJECT_ID = MOMT.AD_MODEL_OBJECT_ID 
                                                AND MOMT.ISACTIVE  = 'Y'
                                                AND MOMT.ISDEFAULT = 'Y'
                   left join AD_MODEL_OBJECT MOF on menuData.AD_WORKFLOW_ID = MOF.AD_WORKFLOW_ID 
                                                AND MOF.ACTION   = 'X' 
                                                AND MOF.ISACTIVE = 'Y'
                                                AND MOF.ISDEFAULT= 'Y'
                   left join AD_MODEL_OBJECT_MAPPING MOMF on MOF.AD_MODEL_OBJECT_ID = MOMF.AD_MODEL_OBJECT_ID 
                                                AND MOMF.ISACTIVE  = 'Y'
                                                AND MOMF.ISDEFAULT = 'Y'
                   left join AD_MODEL_OBJECT MOP on menuData.AD_PROCESS_ID = MOP.AD_PROCESS_ID 
                                                AND MOP.ACTION IN ('P', 'R') 
                                                AND MOP.ISACTIVE = 'Y'
                                                AND MOP.ISDEFAULT= 'Y'
                   left join AD_MODEL_OBJECT_MAPPING MOMP on MOP.AD_MODEL_OBJECT_ID = MOMP.AD_MODEL_OBJECT_ID 
                                                AND MOMP.ISACTIVE  = 'Y'
                                                AND MOMP.ISDEFAULT = 'Y'
                   left join AD_MODEL_OBJECT MOB on menuData.AD_WORKBENCH_ID = MOB.AD_WORKBENCH_ID 
                                                AND MOB.ACTION   = 'B' 
                                                AND MOB.ISACTIVE = 'Y'
                                                AND MOB.ISDEFAULT= 'Y'
                   left join AD_MODEL_OBJECT_MAPPING MOMB on MOB.AD_MODEL_OBJECT_ID = MOMB.AD_MODEL_OBJECT_ID
                                                AND MOMB.ISACTIVE  = 'Y'
                                                AND MOMB.ISDEFAULT = 'Y'
        WHERE tn.node_id = menuData.ad_menu_id 
        AND (EXISTS(SELECT 1 FROM AD_TAB 
        WHERE AD_TAB.isactive ='Y'
        GROUP BY AD_TAB.ad_window_id
        HAVING AD_TAB.ad_window_id=T.ad_window_id 
        AND MIN(AD_TAB.seqno)=T.seqno)
        OR T.ad_tab_id is null)
        AND tn.IsActive='Y'
      </Sql>
      <Parameter name="language"/>
      <Parameter name="adWindowId" optional="true" after="WHERE m.IsActive='Y'" text=" AND m.AD_Window_ID = TO_NUMBER(?) "/>
      <Parameter name="adProcessId" optional="true" after="WHERE m.IsActive='Y'" text=" AND m.AD_Process_ID = TO_NUMBER(?) "/>
      <Parameter name="adFormId" optional="true" after="WHERE m.IsActive='Y'" text=" AND m.AD_FORM_ID = TO_NUMBER(?) "/>
      <Parameter name="adTaskId" optional="true" after="WHERE m.IsActive='Y'" text=" AND m.AD_Task_ID = TO_NUMBER(?) "/>
      <Parameter name="adWorkflowId" optional="true" after="WHERE m.IsActive='Y'" text=" AND m.AD_Workflow_ID = TO_NUMBER(?) "/>
   </SqlMethod>

   <SqlMethod name="selectIdentificacion" type="preparedStatement" return="multiple">
      <SqlMethodComment></SqlMethodComment>
      <Sql>
        SELECT t.AD_Tree_ID AS PARENT_ID, tn.Node_ID
        FROM AD_Tree t, AD_ClientInfo c, AD_TreeNode tn 
        WHERE t.AD_Tree_ID=tn.AD_Tree_ID
        AND tn.Parent_ID IS NULL
        AND t.AD_Tree_ID=c.AD_Tree_Menu_ID
        AND c.AD_Client_ID = TO_NUMBER(?)
      </Sql>
        <Parameter name="strCliente"/>
   </SqlMethod>

   <SqlMethod name="selectSearchs" type="preparedStatement" return="multiple">
      <SqlMethodComment></SqlMethodComment>
      <Sql>
        SELECT NODE_ID, NAME, DESCRIPTION, ClassName
        FROM (
        SELECT R.AD_REFERENCE_ID AS NODE_ID, COALESCE(RT.NAME, R.NAME) AS NAME, 
        COALESCE(RT.DESCRIPTION, R.DESCRIPTION) AS DESCRIPTION, MOM.MAPPINGNAME AS CLASSNAME
        FROM AD_REFERENCE R left join AD_REFERENCE_TRL RT on R.AD_REFERENCE_ID = RT.AD_REFERENCE_ID 
                                                          AND RT.AD_LANGUAGE = ? , 
             AD_MODEL_OBJECT MO, AD_MODEL_OBJECT_MAPPING MOM
        WHERE R.AD_REFERENCE_ID = MO.AD_REFERENCE_ID
        AND MO.AD_MODEL_OBJECT_ID = MOM.AD_MODEL_OBJECT_ID 
        AND MO.ACTION = 'S'
        AND MO.ISACTIVE = 'Y'
        AND MO.ISDEFAULT = 'Y'
        AND MOM.ISACTIVE = 'Y'
        AND MOM.ISDEFAULT = 'Y' 
        ) AA
        ORDER BY NAME
      </Sql>
        <Parameter name="adLanguage"/>        
   </SqlMethod>
   <SqlMethod name="getUserName" type="preparedStatement" return="String" default="">
      <SqlMethodComment></SqlMethodComment>
      <Sql>
        SELECT USERNAME
        FROM AD_USER 
        WHERE AD_User_ID = TO_NUMBER(?)
      </Sql>
        <Parameter name="adUserId"/>
   </SqlMethod>
</SqlClass>