MAXIMO查询菜单并根据权限过滤


查询一级菜单
select t.position ID,
                    ELEMENTTYPE appType,
                    mod.description,
                    NULL PID
               from maxmenu t, maxmodules mod
              where t.keyvalue = mod.module
                and t.menutype = 'MODULE'
                and t.elementtype = 'MODULE'
                and t.visible = 1
                and t.moduleapp in
                    (select m.moduleapp
                       from maxmenu m
                      where m.menutype = 'MODULE'
                        and m.elementtype = 'APP'
                        and m.keyvalue in
                            (select distinct a.app
                               from applicationauth a
                              where a.groupname in
                                    (select g.groupname
                                       from groupuser g
                                      where g.userid = 'ADMIN')
                                and a.optionname = 'READ')) ORDER BY position



查询二级菜单,一级菜单为300000
SELECT * FROM (
(select t.position ID,
                    ELEMENTTYPE appType,
                    t.headerdescription description,
                    '' apptable,
                    m.position PID
               from maxmenu t,
                    (select t.position, mod.module
                       from maxmenu t, maxmodules mod
                      where t.keyvalue = mod.module
                        and t.menutype = 'MODULE'
                        and t.elementtype = 'MODULE'
                        and t.visible = 1
                        and t.moduleapp in
                            (select m.moduleapp
                               from maxmenu m
                              where m.menutype = 'MODULE'
                                and m.elementtype = 'APP'
                                and m.keyvalue in
                                    (select distinct a.app
                                       from applicationauth a
                                      where a.groupname in
                                            (select g.groupname
                                               from groupuser g
                                              where g.userid = 'ADMIN')
                                        and a.optionname = 'READ'))) m,                                       
                          (select POSITION,MODULEAPP,COUNT(1) CONUNTNUM from maxmenu m2,maxapps app where m2.keyvalue=app.app and m2.Elementtype='APP'
                          and M2.keyvalue in
                          (select m.keyvalue
                             from maxmenu m
                            where m.menutype = 'MODULE'
                              and m.keyvalue in
                                  (select distinct a.app
                                     from applicationauth a
                                    where a.groupname in
                                          (select g.groupname
                                             from groupuser g
                                            where g.userid = 'ADMIN')
                                      and a.optionname = 'READ'))
                           GROUP BY POSITION,MODULEAPP HAVING COUNT(1)>0) CON
              where t.menutype = 'MODULE'
                and t.elementtype = 'HEADER'
                and t.moduleapp = m.module
                AND t.position=CON.POSITION
                AND T.moduleapp=CON.moduleapp
                and t.visible = 1
                and m.position='300000'
                and t.moduleapp in
                    (select m.moduleapp
                       from maxmenu m
                      where m.menutype = 'MODULE'
                        and m.keyvalue in
                            (select distinct a.app
                               from applicationauth a
                              where a.groupname in
                                    (select g.groupname
                                       from groupuser g
                                      where g.userid = 'ADMIN')
                                and a.optionname = 'READ')))

            union all
            (select t.position ID,
                    ELEMENTTYPE appType,
                    t.headerdescription description,
                    p.maintbname apptable,
                    m.position PID
               from maxmenu t,
                    maxapps p,
                    (select t.position, mod.module
                       from maxmenu t, maxmodules mod
                      where t.keyvalue = mod.module
                        and t.menutype = 'MODULE'
                        and t.elementtype = 'MODULE'
                        and t.visible = 1
                        and t.moduleapp in
                            (select m.moduleapp
                               from maxmenu m
                              where m.menutype = 'MODULE'
                                and m.elementtype = 'APP'
                                and m.keyvalue in
                                    (select distinct a.app
                                       from applicationauth a
                                      where a.groupname in
                                            (select g.groupname
                                               from groupuser g
                                              where g.userid = 'ADMIN')
                                        and a.optionname = 'READ'))) m
              where t.keyvalue = p.app
                and t.menutype = 'MODULE'
                and t.elementtype = 'APP'
                and t.moduleapp = m.module
                and t.subposition = 0
                and t.visible = 1
                and m.position='200000'
                and t.moduleapp in
                    (select m.moduleapp
                       from maxmenu m
                      where m.menutype = 'MODULE'
                        and m.keyvalue in
                            (select distinct a.app
                               from applicationauth a
                              where a.groupname in
                                    (select g.groupname
                                       from groupuser g
                                      where g.userid = 'ADMIN')
                                and a.optionname = 'READ'))))
                                ORDER BY ID
                               

查询三级菜单,二级菜单为304000
select t.position ID,
                    ELEMENTTYPE appType,
                    p.description description,
                    p.maintbname apptable,
                    pa.position PID
               from maxmenu t,
                    maxapps p,
                    (select t.position, M.module
                       from maxmenu t,
                            (select t.position, mod.module
                               from maxmenu t, maxmodules mod
                              where t.keyvalue = mod.module
                                and t.menutype = 'MODULE'
                                and t.elementtype = 'MODULE'
                                and t.visible = 1
                                and t.moduleapp in
                                    (select m.moduleapp
                                       from maxmenu m
                                      where m.menutype = 'MODULE'
                                        and m.elementtype = 'APP'
                                        and m.keyvalue in
                                            (select distinct a.app
                                               from applicationauth a
                                              where a.groupname in
                                                    (select g.groupname
                                                       from groupuser g
                                                      where g.userid = 'ADMIN')
                                                and a.optionname = 'READ'))) m
                      where t.menutype = 'MODULE'
                        and t.elementtype = 'HEADER'
                        and t.moduleapp = m.module
                        and t.visible = 1
                        and t.moduleapp in
                            (select m.moduleapp
                               from maxmenu m
                              where m.menutype = 'MODULE'
                                and m.keyvalue in
                                    (select distinct a.app
                                       from applicationauth a
                                      where a.groupname in
                                            (select g.groupname
                                               from groupuser g
                                              where g.userid = 'ADMIN')
                                        and a.optionname = 'READ'))) PA
              where t.keyvalue = p.app
                and t.menutype = 'MODULE'
                and t.elementtype = 'APP'
                and t.moduleapp = PA.module
                AND T.position = PA.position
                and t.visible = 1
                and PA.position='304000'
                and t.keyvalue in
                    (select m.keyvalue
                       from maxmenu m
                      where m.menutype = 'MODULE'
                        and m.keyvalue in
                            (select distinct a.app
                               from applicationauth a
                              where a.groupname in
                                    (select g.groupname
                                       from groupuser g
                                      where g.userid = 'ADMIN')
                                and a.optionname = 'READ'))
本站声明
本文转载自:http://awen7916.iteye.com/blog/2204034     作者:awen7916     发布日期:2015-04-20     本站转载的文章为个人学习借鉴使用,本站对版权不负任何法律责任。如果侵犯了您的隐私权益,请联系我们删除。


 
© 2014-2016 ITdaan.com 粤ICP备14056181号