1 local member = param.get("member", "table")

2 local for_member = param.get("for_member", "table")

3 local state = param.get("state")

4 local for_unit = param.get("for_unit", atom.boolean)

5 local for_area = param.get("for_area", atom.boolean)

6

7 local for_events = param.get("for_events", atom.boolean)

8

9 local filters = {}

10

11 local filter = { name = "filter" }

12

13 if state ~= "closed" then

14 filter[#filter+1] = {

15 name = "any",

16 label = _"Any phase",

17 selector_modifier = function(selector) end

18 }

19 end

20

21 if not state then

22 filter[#filter+1] = {

23 name = "open",

24 label = _"Open",

25 selector_modifier = function(selector)

26 if for_events then

27 selector:add_where("event.state in ('admission', 'discussion', 'verification', 'voting')")

28 else

29 selector:add_where("issue.closed ISNULL")

30 end

31 end

32 }

33 end

34

35 if not state or state == "open" then

36 filter[#filter+1] = {

37 name = "new",

38 label = _"New",

39 selector_modifier = function(selector)

40 if for_events then

41 selector:add_where("event.state = 'admission'")

42 else

43 selector:add_where("issue.accepted ISNULL AND issue.closed ISNULL")

44 end

45 end

46 }

47 filter[#filter+1] = {

48 name = "accepted",

49 label = _"Discussion",

50 selector_modifier = function(selector)

51 if for_events then

52 selector:add_where("event.state = 'discussion'")

53 else

54 selector:add_where("issue.accepted NOTNULL AND issue.half_frozen ISNULL AND issue.closed ISNULL")

55 end

56 end

57 }

58 filter[#filter+1] = {

59 name = "half_frozen",

60 label = _"Frozen",

61 selector_modifier = function(selector)

62 if for_events then

63 selector:add_where("event.state = 'verification'")

64 else

65 selector:add_where("issue.half_frozen NOTNULL AND issue.fully_frozen ISNULL")

66 end

67 end

68 }

69 filter[#filter+1] = {

70 name = "frozen",

71 label = _"Voting",

72 selector_modifier = function(selector)

73 if for_events then

74 selector:add_where("event.state = 'voting'")

75 else

76 selector:add_where("issue.fully_frozen NOTNULL AND issue.closed ISNULL")

77 end

78 filter_voting = true

79 end

80 }

81 end

82

83 if not state then

84 filter[#filter+1] = {

85 name = "finished",

86 label = _"Finished",

87 selector_modifier = function(selector)

88 if for_events then

89 selector:add_where("event.state IN ('finished_with_winner', 'finished_without_winner')")

90 else

91 selector:add_where("issue.closed NOTNULL AND issue.fully_frozen NOTNULL")

92 end

93 end

94 }

95 filter[#filter+1] = {

96 name = "cancelled",

97 label = _"Cancelled",

98 selector_modifier = function(selector)

99

100 if for_events then

101 selector:add_where("event.state IN ('canceled_revoked_before_accepted', 'canceled_issue_not_accepted', 'canceled_after_revocation_during_discussion', 'canceled_after_revocation_during_verification')")

102 else

103 selector:add_where("issue.closed NOTNULL AND issue.fully_frozen ISNULL")

104 end

105 end

106 }

107 end

108

109 if state == "closed" then

110 filter[#filter+1] = {

111 name = "any",

112 label = _"Any state",

113 selector_modifier = function(selector) end

114 }

115

116 filter[#filter+1] = {

117 name = "finished",

118 label = _"Finished",

119 selector_modifier = function(selector)

120 if for_events then

121 selector:add_where("event.state IN ('finished_with_winner', 'finished_without_winner')")

122 else

123 selector:add_where("issue.state IN ('finished_with_winner', 'finished_without_winner')")

124 end

125 end

126 }

127 filter[#filter+1] = {

128 name = "finished_with_winner",

129 label = _"with winner",

130 selector_modifier = function(selector)

131 if for_events then

132 selector:add_where("event.state = 'finished_with_winner'")

133 else

134 selector:add_where("issue.state = 'finished_with_winner'")

135 end

136 end

137 }

138 filter[#filter+1] = {

139 name = "finished_without_winner",

140 label = _"without winner",

141 selector_modifier = function(selector)

142 if for_events then

143 selector:add_where("event.state = 'finished_without_winner'")

144 else

145 selector:add_where("issue.state = 'finished_without_winner'")

146 end

147 end

148 }

149 filter[#filter+1] = {

150 name = "cancelled",

151 label = _"Cancelled",

152 selector_modifier = function(selector)

153 if for_events then

154 selector:add_where("event.state NOT IN ('finished_with_winner', 'finished_without_winner')")

155 else

156 selector:add_where("issue.state NOT IN ('finished_with_winner', 'finished_without_winner')")

157 end

158 end

159 }

160 end

161

162 filters[#filters+1] = filter

163

164

165 if member then

166 local filter = {

167 name = "filter_interest",

168 }

169 if not for_member then

170 if not for_unit and not for_area then

171 filter[#filter+1] = {

172 name = "any",

173 label = _"All units",

174 selector_modifier = function() end

175 }

176 filter[#filter+1] = {

177 name = "unit",

178 label = _"My units",

179 selector_modifier = function(selector)

180 selector:join("area", nil, "area.id = issue.area_id")

181 selector:join("privilege", nil, { "privilege.unit_id = area.unit_id AND privilege.member_id = ? AND privilege.voting_right", member.id })

182 end

183 }

184 end

185 if for_unit and not for_area then

186 filter[#filter+1] = {

187 name = "any",

188 label = _"All areas",

189 selector_modifier = function() end

190 }

191 end

192 if not for_area then

193 filter[#filter+1] = {

194 name = "area",

195 label = _"My areas",

196 selector_modifier = function(selector)

197 selector:join("membership", nil, { "membership.area_id = issue.area_id AND membership.member_id = ?", member.id })

198 end

199 }

200 end

201 if for_area then

202 filter[#filter+1] = {

203 name = "any",

204 label = _"All issues",

205 selector_modifier = function() end

206 }

207 end

208 end

209 filter[#filter+1] = {

210 name = "issue",

211 label = _"Interested",

212 selector_modifier = function() end

213 }

214 filter[#filter+1] = {

215 name = "initiated",

216 label = _"Initiated",

217 selector_modifier = function(selector)

218 selector:add_where({ "EXISTS (SELECT 1 FROM initiative JOIN initiator ON initiator.initiative_id = initiative.id AND initiator.member_id = ? AND initiator.accepted WHERE initiative.issue_id = issue.id)", member.id })

219 end

220 }

221 filter[#filter+1] = {

222 name = "supported",

223 label = _"Supported",

224 selector_modifier = function() end

225 }

226 filter[#filter+1] = {

227 name = "potentially_supported",

228 label = _"Potentially supported",

229 selector_modifier = function() end

230 }

231 if state == 'closed' or (for_events) then

232 filter[#filter+1] = {

233 name = "voted",

234 label = _"Voted",

235 selector_modifier = function() end

236 }

237 end

238

239 filters[#filters+1] = filter

240 end

241

242

243

244 if app.session.member then

245

246 local filter_interest = param.get_all_cgi()["filter_interest"]

247

248 if filter_interest ~= "any" and filter_interest ~= nil and (

249 filter_interest == "issue" or filter_interest == "supported" or filter_interest == "potentially_supported" or

250 (filter_interest == 'voted' and state ~= 'open')

251 ) then

252

253 local function add_default_joins(selector)

254 selector:left_join("interest", "filter_interest", { "filter_interest.issue_id = issue.id AND filter_interest.member_id = ? ", member.id })

255 selector:left_join("direct_interest_snapshot", "filter_interest_s", { "filter_interest_s.issue_id = issue.id AND filter_interest_s.member_id = ? AND filter_interest_s.event = issue.latest_snapshot_event", member.id })

256 selector:left_join("delegating_interest_snapshot", "filter_d_interest_s", { "filter_d_interest_s.issue_id = issue.id AND filter_d_interest_s.member_id = ? AND filter_d_interest_s.event = issue.latest_snapshot_event", member.id })

257 end

258

259 filters[#filters+1] = {

260 name = "filter_delegation",

261 {

262 name = "any",

263 label = _"Direct and by delegation",

264 selector_modifier = function(selector)

265 add_default_joins(selector)

266 selector:add_where("CASE WHEN issue.fully_frozen ISNULL AND issue.closed ISNULL THEN filter_interest.member_id NOTNULL ELSE filter_interest_s.member_id NOTNULL END OR filter_d_interest_s.member_id NOTNULL")

267 if filter_interest == "supported" then

268 selector:add_where({

269 "CASE WHEN issue.fully_frozen ISNULL AND issue.closed ISNULL THEN " ..

270 "EXISTS(SELECT 1 FROM initiative JOIN supporter ON supporter.initiative_id = initiative.id AND supporter.member_id = ? LEFT JOIN critical_opinion ON critical_opinion.initiative_id = initiative.id AND critical_opinion.member_id = supporter.member_id WHERE initiative.issue_id = issue.id AND critical_opinion.member_id ISNULL) " ..

271 "ELSE " ..

272 "EXISTS(SELECT 1 FROM direct_supporter_snapshot WHERE direct_supporter_snapshot.event = issue.latest_snapshot_event AND direct_supporter_snapshot.issue_id = issue.id AND direct_supporter_snapshot.member_id = ? AND direct_supporter_snapshot.satisfied) " ..

273 "END OR EXISTS(SELECT 1 FROM direct_supporter_snapshot WHERE direct_supporter_snapshot.event = issue.latest_snapshot_event AND direct_supporter_snapshot.issue_id = issue.id AND direct_supporter_snapshot.member_id = filter_d_interest_s.delegate_member_ids[array_upper(filter_d_interest_s.delegate_member_ids,1)] AND direct_supporter_snapshot.satisfied)", member.id, member.id, member.id })

274

275 elseif filter_interest == "potentially_supported" then

276 selector:add_where({

277 "CASE WHEN issue.fully_frozen ISNULL AND issue.closed ISNULL THEN " ..

278 "EXISTS(SELECT 1 FROM initiative JOIN supporter ON supporter.initiative_id = initiative.id AND supporter.member_id = ? LEFT JOIN critical_opinion ON critical_opinion.initiative_id = initiative.id AND critical_opinion.member_id = supporter.member_id WHERE initiative.issue_id = issue.id AND critical_opinion.member_id NOTNULL) " ..

279 "ELSE " ..

280 "EXISTS(SELECT 1 FROM direct_supporter_snapshot WHERE direct_supporter_snapshot.event = issue.latest_snapshot_event AND direct_supporter_snapshot.issue_id = issue.id AND direct_supporter_snapshot.member_id = ? AND NOT direct_supporter_snapshot.satisfied) " ..

281 "END OR EXISTS(SELECT 1 FROM direct_supporter_snapshot WHERE direct_supporter_snapshot.event = issue.latest_snapshot_event AND direct_supporter_snapshot.issue_id = issue.id AND direct_supporter_snapshot.member_id = filter_d_interest_s.delegate_member_ids[array_upper(filter_d_interest_s.delegate_member_ids,1)] AND NOT direct_supporter_snapshot.satisfied)", member.id, member.id, member.id })

282

283 elseif filter_interest == "voted" then

284 selector:add_where({ "EXISTS(SELECT 1 FROM direct_voter WHERE direct_voter.issue_id = issue.id AND direct_voter.member_id = ?) OR (issue.closed NOTNULL AND EXISTS(SELECT 1 FROM delegating_voter WHERE delegating_voter.issue_id = issue.id AND delegating_voter.member_id = ?)) ", member.id, member.id })

285

286 end

287

288 end

289 },

290 {

291 name = "direct",

292 label = _"Direct",

293 selector_modifier = function(selector)

294 add_default_joins(selector)

295 selector:add_where("CASE WHEN issue.fully_frozen ISNULL AND issue.closed ISNULL THEN filter_interest.member_id NOTNULL ELSE filter_interest_s.member_id NOTNULL END")

296

297 if filter_interest == "supported" then

298 selector:add_where({

299 "CASE WHEN issue.fully_frozen ISNULL AND issue.closed ISNULL THEN " ..

300 "EXISTS(SELECT 1 FROM initiative JOIN supporter ON supporter.initiative_id = initiative.id AND supporter.member_id = ? LEFT JOIN critical_opinion ON critical_opinion.initiative_id = initiative.id AND critical_opinion.member_id = supporter.member_id WHERE initiative.issue_id = issue.id AND critical_opinion.member_id ISNULL) " ..

301 "ELSE " ..

302 "EXISTS(SELECT 1 FROM direct_supporter_snapshot WHERE direct_supporter_snapshot.event = issue.latest_snapshot_event AND direct_supporter_snapshot.issue_id = issue.id AND direct_supporter_snapshot.member_id = ? AND direct_supporter_snapshot.satisfied) " ..

303 "END", member.id, member.id })

304

305 elseif filter_interest == "potentially_supported" then

306 selector:add_where({

307 "CASE WHEN issue.fully_frozen ISNULL AND issue.closed ISNULL THEN " ..

308 "EXISTS(SELECT 1 FROM initiative JOIN supporter ON supporter.initiative_id = initiative.id AND supporter.member_id = ? LEFT JOIN critical_opinion ON critical_opinion.initiative_id = initiative.id AND critical_opinion.member_id = supporter.member_id WHERE initiative.issue_id = issue.id AND critical_opinion.member_id NOTNULL) " ..

309 "ELSE " ..

310 "EXISTS(SELECT 1 FROM direct_supporter_snapshot WHERE direct_supporter_snapshot.event = issue.latest_snapshot_event AND direct_supporter_snapshot.issue_id = issue.id AND direct_supporter_snapshot.member_id = ? AND NOT direct_supporter_snapshot.satisfied) " ..

311 "END", member.id, member.id })

312 elseif filter_interest == "voted" then

313 selector:add_where({ "EXISTS(SELECT 1 FROM direct_voter WHERE direct_voter.issue_id = issue.id AND direct_voter.member_id = ?) ", member.id })

314

315 end

316 end

317 },

318 {

319 name = "delegated",

320 label = _"By delegation",

321 selector_modifier = function(selector)

322 add_default_joins(selector)

323 selector:add_where("filter_d_interest_s.member_id NOTNULL AND filter_interest.member_id ISNULL")

324

325 if filter_interest == "supported" then

326 selector:add_where({

327 "EXISTS(SELECT 1 FROM direct_supporter_snapshot WHERE direct_supporter_snapshot.event = issue.latest_snapshot_event AND direct_supporter_snapshot.issue_id = issue.id AND direct_supporter_snapshot.member_id = filter_d_interest_s.delegate_member_ids[array_upper(filter_d_interest_s.delegate_member_ids,1)] AND direct_supporter_snapshot.satisfied)", member.id })

328

329 elseif filter_interest == "potentially_supported" then

330 selector:add_where({

331 "EXISTS(SELECT 1 FROM direct_supporter_snapshot WHERE direct_supporter_snapshot.event = issue.latest_snapshot_event AND direct_supporter_snapshot.issue_id = issue.id AND direct_supporter_snapshot.member_id = filter_d_interest_s.delegate_member_ids[array_upper(filter_d_interest_s.delegate_member_ids,1)] AND NOT direct_supporter_snapshot.satisfied)", member.id })

332 elseif filter_interest == "voted" then

333 selector:add_where({ "issue.closed NOTNULL AND EXISTS(SELECT 1 FROM delegating_voter WHERE delegating_voter.issue_id = issue.id AND delegating_voter.member_id = ?) ", member.id })

334

335 end

336 end

337 }

338 }

339 end

340

341 end

342

343 if not for_events and app.session.member and member.id == app.session.member_id and (param.get_all_cgi()["filter"] == "frozen") then

344 filters[#filters+1] = {

345 name = "filter_voting",

346 {

347 name = "any",

348 label = _"Any",

349 selector_modifier = function() end

350 },

351 {

352 name = "not_voted",

353 label = _"Not voted",

354 selector_modifier = function(selector)

355 selector:left_join("direct_voter", nil, { "direct_voter.issue_id = issue.id AND direct_voter.member_id = ?", member.id })

356 selector:add_where("direct_voter.member_id ISNULL")

357 end

358 },

359 {

360 name = "voted",

361 label = _"Voted",

362 selector_modifier = function(selector)

363 selector:join("direct_voter", nil, { "direct_voter.issue_id = issue.id AND direct_voter.member_id = ?", member.id })

364 end

365 },

366 }

367 end

368

369

370

371

372 function filters:get_filter(group, name)

373 for i,grp in ipairs(self) do

374 if grp.name == group then

375 for i,entry in ipairs(grp) do

376 if entry.name == name then

377 return entry

378 end

379 end

380 end

381 end

382 end

383

384 return filters