1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
|
SELECT us.id_user ,us.nom, count(distinct(id_consult)) as totale,nbr_vu_action,c.owner_info
,(select REGEXP_REPLACE(json_extract(blocked
,concat('$.'
,(case
when c.type_info in('pub','com','s_com','2s_com','3s_com','img couverture','img profil') then 'pub'
when c.type_info in('amitie') then 'invite_ami'
when c.type_info in('created_groupe','groupe','pub_groupe','com_groupe','s_com_groupe') then 'pub_groupe' end)
,'.ids')
),'[^0-9,\\\[\\\]]','') from users where id_user=c.owner_info
) as blocked
,(select REGEXP_REPLACE(json_extract(autorised
,concat('$.'
,(case
when c.type_info in('pub','com','s_com','2s_com','3s_com','img couverture','img profil') then 'pub'
when c.type_info in('amitie') then 'invite_ami'
when c.type_info in('created_groupe','groupe','pub_groupe','com_groupe','s_com_groupe') then 'pub_groupe' end)
,'.ids')
),'[^0-9,\\\[\\\]]','') from users where id_user=c.owner_info
) as autorised
,(select REGEXP_REPLACE(json_extract(blocked
,concat('$.'
,(case
when c.type_info in('pub','com','s_com','2s_com','3s_com','img couverture','img profil') then 'pub'
when c.type_info in('amitie') then 'invite_ami'
when c.type_info in('created_groupe','groupe','pub_groupe','com_groupe','s_com_groupe') then 'pub_groupe' end)
,'.ids')
),'[^0-9,\\\[\\\]]','') from users where id_user=us.id_user
) as myBlocked
,(select REGEXP_REPLACE(json_extract(autorised
,concat('$.'
,(case
when c.type_info in('pub','com','s_com','2s_com','3s_com','img couverture','img profil') then 'pub'
when c.type_info in('amitie') then 'invite_ami'
when c.type_info in('created_groupe','groupe','pub_groupe','com_groupe','s_com_groupe') then 'pub_groupe' end)
,'.ids')
),'[^0-9,\\\[\\\]]','') from users where id_user=us.id_user
) as myAutorised
,(select REGEXP_REPLACE(json_extract(autorised
,concat('$.'
,case
when c.type_info in('pub','com','s_com','2s_com','3s_com','img couverture','img profil') then 'pub'
when c.type_info in('amitie') then 'invite_ami'
when c.type_info in('created_groupe','groupe','pub_groupe','com_groupe','s_com_groupe') then 'pub_groupe' end
,'.valeur_param')
),'[^0-9,\\\[\\\]]','') from users where id_user=us.id_user
) as myValeurparam
,(select REGEXP_REPLACE(json_extract(autorised
,concat('$.'
,case
when c.type_info in('pub','com','s_com','2s_com','3s_com','img couverture','img profil') then 'pub'
when c.type_info in('amitie') then 'invite_ami'
when c.type_info in('created_groupe','groupe','pub_groupe','com_groupe','s_com_groupe') then 'pub_groupe' end
,'.valeur_param')
),'[^0-9,\\\[\\\]]','') from users where id_user=c.owner_info
) as valeur_param
FROM consultation c
join `users` us
left join `invitation` inv on(c.type_info in('created_groupe','groupe','pub_groupe','com_groupe','s_com_groupe'))
left join `groupe` grp on c.type_info in('created_groupe','groupe','pub_groupe','com_groupe','s_com_groupe')
left join `pub_groupe` p_groupe on(c.type_info='pub_groupe' and p_groupe.id_pub=c.id_info and grp.id_groupe=p_groupe.id_groupe )
left join `comments_p_grp` com_pg on(c.type_info='com_groupe' and com_pg.id_com=c.id_info )
left join `s_comments_p_grp` s_com_pg on( c.type_info='s_com_groupe' and s_com_pg.id_s_com=c.id_info and grp.id_groupe=p_groupe.id_groupe)
left join `abonnement` ab
on (
c.type_info in('groupe','pub_groupe','com_groupe','s_com_groupe','created_groupe')
and
(
(
ab.id_info_abn=grp.id_groupe
and (
case c.type_info
when 'groupe' then ab.id_info_abn=grp.id_groupe
when 'pub_groupe' then ab.id_info_abn=p_groupe.id_groupe
when 'com_groupe' then grp.id_groupe=(select id_groupe from pub_groupe p_g where p_g.id_pub=com_pg.id_pub )
when 's_com_groupe' then
grp.id_groupe = (select id_groupe from `s_comments_p_grp` scpg
inner join `comments_p_grp` cpg on (cpg.id_com=scpg.id_com)
inner join `pub_groupe` p_g on (p_g.id_pub=cpg.id_pub)
where scpg.id_s_com=c.id_info )
end
)
)
or (c.type_info='created_groupe' and c.id_info=grp.id_groupe)
)
)
inner join relation r
on (
((r.id_emet=c.owner_info and r.id_dest=us.id_user) or (r.id_emet=us.id_user and r.id_dest=c.owner_info))
and (c.owner_info!=us.id_user
and (
(c.type_info not in('created_groupe','groupe','amitie','pub_groupe','com_groupe','s_com_groupe') and r.etat=1)
or (c.type_info in('created_groupe','pub_groupe','com_groupe','s_com_groupe') and inv.etat=1 and ab.id_user=us.id_user )
or (c.type_info in('amitie') and r.id_relation=c.id_info and r.id_dest=us.id_user)
or (c.type_info in('groupe') and inv.id_inv=c.id_info and inv.emet_inv=c.owner_info and inv.dest_inv=us.id_user)
)
)
)
or (c.owner_info=us.id_user and c.type_info in('amitie','groupe') and r.id_emet=us.id_user )
group by c.id_consult,us.nom,us.id_user
having
(
case when c.owner_info != us.id_user then
case valeur_param
when 1 or 2 then
(case myValeurparam
when 1 or 2 then true
when 3 then
case when json_contains(myAutorised,c.owner_info)=1 then true
end
when 4 then
case
when json_contains(myBlocked,c.owner_info)=0 then true
else false
end
when 5 then false
end)
when 3 then json_contains(autorised,us.id_user)=1 and
(case myValeurparam
when 1 then true
when 2 then true
when 3 then
case when json_contains(myAutorised,c.owner_info)=1 then true
end
when 4 then
case
when json_contains(myBlocked,c.owner_info)=0 then true
else false
end
when 5 then false
end)
when 4 then json_contains(blocked,us.id_user)=0 and
(case myValeurparam
when 1 then true
when 2 then true
when 3 then
case when json_contains(myAutorised,c.owner_info)=1 then true
end
when 4 then
case
when json_contains(myBlocked,c.owner_info)=0 then true
else false
end
when 5 then false
end)
when 5 then false
else false
end
when c.owner_info=us.id_user then true
end
)
order by us.id_user asc; |
