Skip to content

Instantly share code, notes, and snippets.

/query1.sql

Created February 2, 2016 17:26
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save anonymous/90ce4200a5849616413e to your computer and use it in GitHub Desktop.
Save anonymous/90ce4200a5849616413e to your computer and use it in GitHub Desktop.
Download ZIP
Raw
query1.sql
select RES.*
from
ACT_HI_PROCINST RES
WHERE PROC_DEF_ID_ = $1
AND (
SELECT
CASE
WHEN
RES.PROC_DEF_KEY_ IN
(SELECT
A.RESOURCE_ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 1
AND
A.USER_ID_ = $2
AND
A.PERMS_ & $3 = $4
AND
A.RESOURCE_TYPE_ = $5
AND
A.RESOURCE_ID_ = RES.PROC_DEF_KEY_)
THEN 1
WHEN
RES.PROC_DEF_KEY_ IN
(SELECT
A.RESOURCE_ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 2
AND
A.USER_ID_ = $6
AND
A.PERMS_ & $7 != $8
AND
A.RESOURCE_TYPE_ = $9
AND
A.RESOURCE_ID_ = RES.PROC_DEF_KEY_)
THEN 0
WHEN
EXISTS
(SELECT
ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 1
AND
A.USER_ID_ = $10
AND
A.PERMS_ & $11 = $12
AND
A.RESOURCE_TYPE_ = $13
AND
A.RESOURCE_ID_ = '*')
THEN 1
WHEN
EXISTS
(SELECT
ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 2
AND
A.USER_ID_ = $14
AND
A.PERMS_ & $15 != $16
AND
A.RESOURCE_TYPE_ = $17
AND
A.RESOURCE_ID_ = '*')
THEN 0
ELSE
(
SELECT
CASE
WHEN
RES.PROC_DEF_KEY_ IN
(SELECT
A.RESOURCE_ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 1
AND
A.GROUP_ID_ = $18
AND
A.PERMS_ & $19 = $20
AND
A.RESOURCE_TYPE_ = $21
AND
A.RESOURCE_ID_ = RES.PROC_DEF_KEY_)
THEN 1
WHEN
RES.PROC_DEF_KEY_ IN
(SELECT
A.RESOURCE_ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 1
AND
A.GROUP_ID_ = $22
AND
A.PERMS_ & $23 = $24
AND
A.RESOURCE_TYPE_ = $25
AND
A.RESOURCE_ID_ = RES.PROC_DEF_KEY_)
THEN 1
WHEN
RES.PROC_DEF_KEY_ IN
(SELECT
A.RESOURCE_ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 1
AND
A.GROUP_ID_ = $26
AND
A.PERMS_ & $27 = $28
AND
A.RESOURCE_TYPE_ = $29
AND
A.RESOURCE_ID_ = RES.PROC_DEF_KEY_)
THEN 1
WHEN
RES.PROC_DEF_KEY_ IN
(SELECT
A.RESOURCE_ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 1
AND
A.GROUP_ID_ = $30
AND
A.PERMS_ & $31 = $32
AND
A.RESOURCE_TYPE_ = $33
AND
A.RESOURCE_ID_ = RES.PROC_DEF_KEY_)
THEN 1
WHEN
RES.PROC_DEF_KEY_ IN
(SELECT
A.RESOURCE_ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 2
AND
A.GROUP_ID_ = $34
AND
A.PERMS_ & $35 != $36
AND
A.RESOURCE_TYPE_ = $37
AND
A.RESOURCE_ID_ = RES.PROC_DEF_KEY_)
THEN 0
WHEN
RES.PROC_DEF_KEY_ IN
(SELECT
A.RESOURCE_ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 2
AND
A.GROUP_ID_ = $38
AND
A.PERMS_ & $39 != $40
AND
A.RESOURCE_TYPE_ = $41
AND
A.RESOURCE_ID_ = RES.PROC_DEF_KEY_)
THEN 0
WHEN
RES.PROC_DEF_KEY_ IN
(SELECT
A.RESOURCE_ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 2
AND
A.GROUP_ID_ = $42
AND
A.PERMS_ & $43 != $44
AND
A.RESOURCE_TYPE_ = $45
AND
A.RESOURCE_ID_ = RES.PROC_DEF_KEY_)
THEN 0
WHEN
RES.PROC_DEF_KEY_ IN
(SELECT
A.RESOURCE_ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 2
AND
A.GROUP_ID_ = $46
AND
A.PERMS_ & $47 != $48
AND
A.RESOURCE_TYPE_ = $49
AND
A.RESOURCE_ID_ = RES.PROC_DEF_KEY_)
THEN 0
WHEN
EXISTS
(SELECT
ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 1
AND
A.GROUP_ID_ = $50
AND
A.PERMS_ & $51 = $52
AND
A.RESOURCE_TYPE_ = $53
AND
A.RESOURCE_ID_ = '*')
THEN 1
WHEN
EXISTS
(SELECT
ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 1
AND
A.GROUP_ID_ = $54
AND
A.PERMS_ & $55 = $56
AND
A.RESOURCE_TYPE_ = $57
AND
A.RESOURCE_ID_ = '*')
THEN 1
WHEN
EXISTS
(SELECT
ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 1
AND
A.GROUP_ID_ = $58
AND
A.PERMS_ & $59 = $60
AND
A.RESOURCE_TYPE_ = $61
AND
A.RESOURCE_ID_ = '*')
THEN 1
WHEN
EXISTS
(SELECT
ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 1
AND
A.GROUP_ID_ = $62
AND
A.PERMS_ & $63 = $64
AND
A.RESOURCE_TYPE_ = $65
AND
A.RESOURCE_ID_ = '*')
THEN 1
WHEN
EXISTS
(SELECT
ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 2
AND
A.GROUP_ID_ = $66
AND
A.PERMS_ & $67 != $68
AND
A.RESOURCE_TYPE_ = $69
AND
A.RESOURCE_ID_ = '*')
THEN 0
WHEN
EXISTS
(SELECT
ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 2
AND
A.GROUP_ID_ = $70
AND
A.PERMS_ & $71 != $72
AND
A.RESOURCE_TYPE_ = $73
AND
A.RESOURCE_ID_ = '*')
THEN 0
WHEN
EXISTS
(SELECT
ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 2
AND
A.GROUP_ID_ = $74
AND
A.PERMS_ & $75 != $76
AND
A.RESOURCE_TYPE_ = $77
AND
A.RESOURCE_ID_ = '*')
THEN 0
WHEN
EXISTS
(SELECT
ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 2
AND
A.GROUP_ID_ = $78
AND
A.PERMS_ & $79 != $80
AND
A.RESOURCE_TYPE_ = $81
AND
A.RESOURCE_ID_ = '*')
THEN 0
ELSE (
SELECT
CASE
WHEN
RES.PROC_DEF_KEY_ IN
(SELECT
A.RESOURCE_ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 0
AND
A.USER_ID_ = '*'
AND
A.PERMS_ & $82 = $83
AND
A.RESOURCE_TYPE_ = $84
AND
A.RESOURCE_ID_ = RES.PROC_DEF_KEY_)
THEN 1
WHEN
RES.PROC_DEF_KEY_ IN
(SELECT
A.RESOURCE_ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 0
AND
A.USER_ID_ = '*'
AND
A.PERMS_ & $85 != $86
AND
A.RESOURCE_TYPE_ = $87
AND
A.RESOURCE_ID_ = RES.PROC_DEF_KEY_)
THEN 0
WHEN
EXISTS
(SELECT
ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 0
AND
A.USER_ID_ = '*'
AND
A.PERMS_ & $88 = $89
AND
A.RESOURCE_TYPE_ = $90
AND
A.RESOURCE_ID_ = '*')
THEN 1
WHEN
EXISTS
(SELECT
ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 0
AND
A.USER_ID_ = '*'
AND
A.PERMS_ & $91 != $92
AND
A.RESOURCE_TYPE_ = $93
AND
A.RESOURCE_ID_ = '*')
THEN 0
ELSE
null
END
)
END
)
END
) = 1
order by START_TIME_ desc
LIMIT $94 OFFSET $95
Raw
query2.sql
select count(distinct RES.ID_)
from
ACT_HI_PROCINST RES
WHERE PROC_DEF_ID_ = $1
AND (
SELECT
CASE
WHEN
RES.PROC_DEF_KEY_ IN
(SELECT
A.RESOURCE_ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 1
AND
A.USER_ID_ = $2
AND
A.PERMS_ & $3 = $4
AND
A.RESOURCE_TYPE_ = $5
AND
A.RESOURCE_ID_ = RES.PROC_DEF_KEY_)
THEN 1
WHEN
RES.PROC_DEF_KEY_ IN
(SELECT
A.RESOURCE_ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 2
AND
A.USER_ID_ = $6
AND
A.PERMS_ & $7 != $8
AND
A.RESOURCE_TYPE_ = $9
AND
A.RESOURCE_ID_ = RES.PROC_DEF_KEY_)
THEN 0
WHEN
EXISTS
(SELECT
ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 1
AND
A.USER_ID_ = $10
AND
A.PERMS_ & $11 = $12
AND
A.RESOURCE_TYPE_ = $13
AND
A.RESOURCE_ID_ = '*')
THEN 1
WHEN
EXISTS
(SELECT
ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 2
AND
A.USER_ID_ = $14
AND
A.PERMS_ & $15 != $16
AND
A.RESOURCE_TYPE_ = $17
AND
A.RESOURCE_ID_ = '*')
THEN 0
ELSE
(
SELECT
CASE
WHEN
RES.PROC_DEF_KEY_ IN
(SELECT
A.RESOURCE_ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 1
AND
A.GROUP_ID_ = $18
AND
A.PERMS_ & $19 = $20
AND
A.RESOURCE_TYPE_ = $21
AND
A.RESOURCE_ID_ = RES.PROC_DEF_KEY_)
THEN 1
WHEN
RES.PROC_DEF_KEY_ IN
(SELECT
A.RESOURCE_ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 1
AND
A.GROUP_ID_ = $22
AND
A.PERMS_ & $23 = $24
AND
A.RESOURCE_TYPE_ = $25
AND
A.RESOURCE_ID_ = RES.PROC_DEF_KEY_)
THEN 1
WHEN
RES.PROC_DEF_KEY_ IN
(SELECT
A.RESOURCE_ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 1
AND
A.GROUP_ID_ = $26
AND
A.PERMS_ & $27 = $28
AND
A.RESOURCE_TYPE_ = $29
AND
A.RESOURCE_ID_ = RES.PROC_DEF_KEY_)
THEN 1
WHEN
RES.PROC_DEF_KEY_ IN
(SELECT
A.RESOURCE_ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 1
AND
A.GROUP_ID_ = $30
AND
A.PERMS_ & $31 = $32
AND
A.RESOURCE_TYPE_ = $33
AND
A.RESOURCE_ID_ = RES.PROC_DEF_KEY_)
THEN 1
WHEN
RES.PROC_DEF_KEY_ IN
(SELECT
A.RESOURCE_ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 2
AND
A.GROUP_ID_ = $34
AND
A.PERMS_ & $35 != $36
AND
A.RESOURCE_TYPE_ = $37
AND
A.RESOURCE_ID_ = RES.PROC_DEF_KEY_)
THEN 0
WHEN
RES.PROC_DEF_KEY_ IN
(SELECT
A.RESOURCE_ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 2
AND
A.GROUP_ID_ = $38
AND
A.PERMS_ & $39 != $40
AND
A.RESOURCE_TYPE_ = $41
AND
A.RESOURCE_ID_ = RES.PROC_DEF_KEY_)
THEN 0
WHEN
RES.PROC_DEF_KEY_ IN
(SELECT
A.RESOURCE_ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 2
AND
A.GROUP_ID_ = $42
AND
A.PERMS_ & $43 != $44
AND
A.RESOURCE_TYPE_ = $45
AND
A.RESOURCE_ID_ = RES.PROC_DEF_KEY_)
THEN 0
WHEN
RES.PROC_DEF_KEY_ IN
(SELECT
A.RESOURCE_ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 2
AND
A.GROUP_ID_ = $46
AND
A.PERMS_ & $47 != $48
AND
A.RESOURCE_TYPE_ = $49
AND
A.RESOURCE_ID_ = RES.PROC_DEF_KEY_)
THEN 0
WHEN
EXISTS
(SELECT
ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 1
AND
A.GROUP_ID_ = $50
AND
A.PERMS_ & $51 = $52
AND
A.RESOURCE_TYPE_ = $53
AND
A.RESOURCE_ID_ = '*')
THEN 1
WHEN
EXISTS
(SELECT
ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 1
AND
A.GROUP_ID_ = $54
AND
A.PERMS_ & $55 = $56
AND
A.RESOURCE_TYPE_ = $57
AND
A.RESOURCE_ID_ = '*')
THEN 1
WHEN
EXISTS
(SELECT
ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 1
AND
A.GROUP_ID_ = $58
AND
A.PERMS_ & $59 = $60
AND
A.RESOURCE_TYPE_ = $61
AND
A.RESOURCE_ID_ = '*')
THEN 1
WHEN
EXISTS
(SELECT
ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 1
AND
A.GROUP_ID_ = $62
AND
A.PERMS_ & $63 = $64
AND
A.RESOURCE_TYPE_ = $65
AND
A.RESOURCE_ID_ = '*')
THEN 1
WHEN
EXISTS
(SELECT
ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 2
AND
A.GROUP_ID_ = $66
AND
A.PERMS_ & $67 != $68
AND
A.RESOURCE_TYPE_ = $69
AND
A.RESOURCE_ID_ = '*')
THEN 0
WHEN
EXISTS
(SELECT
ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 2
AND
A.GROUP_ID_ = $70
AND
A.PERMS_ & $71 != $72
AND
A.RESOURCE_TYPE_ = $73
AND
A.RESOURCE_ID_ = '*')
THEN 0
WHEN
EXISTS
(SELECT
ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 2
AND
A.GROUP_ID_ = $74
AND
A.PERMS_ & $75 != $76
AND
A.RESOURCE_TYPE_ = $77
AND
A.RESOURCE_ID_ = '*')
THEN 0
WHEN
EXISTS
(SELECT
ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 2
AND
A.GROUP_ID_ = $78
AND
A.PERMS_ & $79 != $80
AND
A.RESOURCE_TYPE_ = $81
AND
A.RESOURCE_ID_ = '*')
THEN 0
ELSE (
SELECT
CASE
WHEN
RES.PROC_DEF_KEY_ IN
(SELECT
A.RESOURCE_ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 0
AND
A.USER_ID_ = '*'
AND
A.PERMS_ & $82 = $83
AND
A.RESOURCE_TYPE_ = $84
AND
A.RESOURCE_ID_ = RES.PROC_DEF_KEY_)
THEN 1
WHEN
RES.PROC_DEF_KEY_ IN
(SELECT
A.RESOURCE_ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 0
AND
A.USER_ID_ = '*'
AND
A.PERMS_ & $85 != $86
AND
A.RESOURCE_TYPE_ = $87
AND
A.RESOURCE_ID_ = RES.PROC_DEF_KEY_)
THEN 0
WHEN
EXISTS
(SELECT
ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 0
AND
A.USER_ID_ = '*'
AND
A.PERMS_ & $88 = $89
AND
A.RESOURCE_TYPE_ = $90
AND
A.RESOURCE_ID_ = '*')
THEN 1
WHEN
EXISTS
(SELECT
ID_
FROM
ACT_RU_AUTHORIZATION A
WHERE
A.TYPE_ = 0
AND
A.USER_ID_ = '*'
AND
A.PERMS_ & $91 != $92
AND
A.RESOURCE_TYPE_ = $93
AND
A.RESOURCE_ID_ = '*')
THEN 0
ELSE
null
END
)
END
)
END
) = 1
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment