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

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

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

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

5

6 local filters = {}

7

8 if member then

9 local filter = {

10 name = "filter_interest",

11 }

12 if not for_unit and not for_area then

13 filter[#filter+1] = {

14 name = "any",

15 label = _"All units",

16 selector_modifier = function() end

17 }

18 filter[#filter+1] = {

19 name = "unit",

20 label = _"My units",

21 selector_modifier = function(selector)

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

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

24 end

25 }

26 end

27 if for_unit and not for_area then

28 filter[#filter+1] = {

29 name = "any",

30 label = _"All areas",

31 selector_modifier = function() end

32 }

33 end

34 if not for_area then

35 filter[#filter+1] = {

36 name = "area",

37 label = _"My areas",

38 selector_modifier = function(selector)

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

40 end

41 }

42 end

43 if for_area then

44 filter[#filter+1] = {

45 name = "any",

46 label = _"All issues",

47 selector_modifier = function() end

48 }

49 end

50 filter[#filter+1] = {

51 name = "issue",

52 label = _"Interested",

53 selector_modifier = function() end

54 }

55 filter[#filter+1] = {

56 name = "supported",

57 label = _"Supported",

58 selector_modifier = function() end

59 }

60 filter[#filter+1] = {

61 name = "potentially_supported",

62 label = _"Potentially supported",

63 selector_modifier = function() end

64 }

65 filter[#filter+1] = {

66 name = "initiated",

67 label = _"Initiated",

68 selector_modifier = function(selector)

69 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 })

70 end

71 }

72

73 filters[#filters+1] = filter

74 end

75

76

77

78 if app.session.member then

79

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

81

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

83 filter_interest == "issue" or filter_interest == "supported" or filter_interest == "potentially_supported"

84 ) then

85 filters[#filters+1] = {

86 name = "filter_delegation",

87 {

88 name = "any",

89 label = _"Direct and by delegation",

90 selector_modifier = function(selector)

91 if filter_interest == "issue" then

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

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

94 selector:add_where{ "filter_interest.member_id NOTNULL OR filter_delegating_interest.member_id NOTNULL" }

95 elseif filter_interest == "supported" then

96 selector:add_where({ "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 = ? WHERE initiative.issue_id = issue.id AND critical_opinion.member_id ISNULL LIMIT 1) OR EXISTS (SELECT 1 FROM initiative JOIN direct_supporter_snapshot ON direct_supporter_snapshot.initiative_id = initiative.id AND direct_supporter_snapshot.event = issue.latest_snapshot_event JOIN delegating_interest_snapshot ON delegating_interest_snapshot.delegate_member_ids[array_upper(delegating_interest_snapshot.delegate_member_ids, 1)] = direct_supporter_snapshot.member_id AND delegating_interest_snapshot.issue_id = issue.id AND delegating_interest_snapshot.member_id = ? AND delegating_interest_snapshot.event = issue.latest_snapshot_event WHERE initiative.issue_id = issue.id AND direct_supporter_snapshot.satisfied LIMIT 1)", member.id, member.id, member.id })

97 elseif filter_interest == "potentially_supported" then

98 selector:add_where({ "EXISTS (SELECT 1 FROM initiative JOIN supporter ON supporter.initiative_id = initiative.id AND supporter.member_id = ? JOIN critical_opinion ON critical_opinion.initiative_id = initiative.id AND critical_opinion.member_id = ? WHERE initiative.issue_id = issue.id LIMIT 1) OR EXISTS (SELECT 1 FROM initiative JOIN direct_supporter_snapshot ON direct_supporter_snapshot.initiative_id = initiative.id AND direct_supporter_snapshot.event = issue.latest_snapshot_event JOIN delegating_interest_snapshot ON delegating_interest_snapshot.delegate_member_ids[array_upper(delegating_interest_snapshot.delegate_member_ids, 1)] = direct_supporter_snapshot.member_id AND delegating_interest_snapshot.issue_id = issue.id AND delegating_interest_snapshot.member_id = ? AND delegating_interest_snapshot.event = issue.latest_snapshot_event WHERE initiative.issue_id = issue.id AND NOT direct_supporter_snapshot.satisfied LIMIT 1)", member.id, member.id, member.id, member.id })

99 end

100 end

101 },

102 {

103 name = "direct",

104 label = _"Direct",

105 selector_modifier = function(selector)

106 if filter_interest == "issue" then

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

108 elseif filter_interest == "supported" then

109 selector:add_where({ "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 = ? WHERE initiative.issue_id = issue.id AND critical_opinion.member_id ISNULL LIMIT 1)", member.id, member.id })

110 elseif filter_interest == "potentially_supported" then

111 selector:add_where({ "EXISTS (SELECT 1 FROM initiative JOIN supporter ON supporter.initiative_id = initiative.id AND supporter.member_id = ? JOIN critical_opinion ON critical_opinion.initiative_id = initiative.id AND critical_opinion.member_id = ? WHERE initiative.issue_id = issue.id LIMIT 1)", member.id, member.id })

112 end

113 end

114 },

115 {

116 name = "delegated",

117 label = _"By delegation",

118 selector_modifier = function(selector)

119 if filter_interest == "issue" then

120 selector:join("delegating_interest_snapshot", "filter_interest", { "filter_interest.issue_id = issue.id AND filter_interest.member_id = ? AND filter_interest.event = issue.latest_snapshot_event", member.id })

121 elseif filter_interest == "supported" then

122 selector:add_where({ "EXISTS (SELECT 1 FROM initiative JOIN direct_supporter_snapshot ON direct_supporter_snapshot.initiative_id = initiative.id AND direct_supporter_snapshot.event = issue.latest_snapshot_event JOIN delegating_interest_snapshot ON delegating_interest_snapshot.delegate_member_ids[array_upper(delegating_interest_snapshot.delegate_member_ids, 1)] = direct_supporter_snapshot.member_id AND delegating_interest_snapshot.issue_id = issue.id AND delegating_interest_snapshot.member_id = ? AND delegating_interest_snapshot.event = issue.latest_snapshot_event WHERE initiative.issue_id = issue.id AND direct_supporter_snapshot.satisfied LIMIT 1)", member.id })

123 elseif filter_interest == "potentially_supported" then

124 selector:add_where({ "EXISTS (SELECT 1 FROM initiative JOIN direct_supporter_snapshot ON direct_supporter_snapshot.initiative_id = initiative.id AND direct_supporter_snapshot.event = issue.latest_snapshot_event JOIN delegating_interest_snapshot ON delegating_interest_snapshot.delegate_member_ids[array_upper(delegating_interest_snapshot.delegate_member_ids, 1)] = direct_supporter_snapshot.member_id AND delegating_interest_snapshot.issue_id = issue.id AND delegating_interest_snapshot.member_id = ? AND delegating_interest_snapshot.event = issue.latest_snapshot_event WHERE initiative.issue_id = issue.id AND NOT direct_supporter_snapshot.satisfied LIMIT 1)", member.id, member.id })

125 end

126 end

127 }

128 }

129 end

130

131 end

132

133 local filter = { name = "filter" }

134

135 filter[#filter+1] = {

136 name = "any",

137 label = _"Any phase",

138 selector_modifier = function(selector) end

139 }

140

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

142 filter[#filter+1] = {

143 name = "open",

144 label = _"Open",

145 selector_modifier = function(selector)

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

147 end

148 }

149 filter[#filter+1] = {

150 name = "new",

151 label = _"New",

152 selector_modifier = function(selector)

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

154 end

155 }

156 filter[#filter+1] = {

157 name = "accepted",

158 label = _"Discussion",

159 selector_modifier = function(selector)

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

161 end

162 }

163 filter[#filter+1] = {

164 name = "half_frozen",

165 label = _"Frozen",

166 selector_modifier = function(selector)

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

168 end

169 }

170 filter[#filter+1] = {

171 name = "frozen",

172 label = _"Voting",

173 selector_modifier = function(selector)

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

175 filter_voting = true

176 end

177 }

178 end

179

180 if not state then

181 filter[#filter+1] = {

182 name = "finished",

183 label = _"Finished",

184 selector_modifier = function(selector)

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

186 end

187 }

188 filter[#filter+1] = {

189 name = "cancelled",

190 label = _"Cancelled",

191 selector_modifier = function(selector)

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

193 end

194 }

195 end

196

197 if state == "closed" then

198 filter[#filter+1] = {

199 name = "finished_with_winner",

200 label = _"Finished with winner",

201 selector_modifier = function(selector)

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

203 end

204 }

205 filter[#filter+1] = {

206 name = "finished_without_winner",

207 label = _"Finished without winner",

208 selector_modifier = function(selector)

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

210 end

211 }

212 filter[#filter+1] = {

213 name = "cancelled",

214 label = _"Cancelled",

215 selector_modifier = function(selector)

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

217 end

218 }

219 end

220

221 filters[#filters+1] = filter

222

223 --[[

224 if not param.get("no_sort", atom.boolean) then

225

226 local filter = { name = "order" }

227

228 local text = _"Time left"

229 local f = param.get_all_cgi()["filter"]

230 if f == "finished" or f == "cancelled" then

231 text = _"Recently closed"

232 end

233 filter[#filter+1] = {

234 name = "state_time",

235 label = text,

236 selector_modifier = function(selector)

237 selector:add_order_by("issue.closed DESC, coalesce(issue.fully_frozen + issue.voting_time, issue.half_frozen + issue.verification_time, issue.accepted + issue.discussion_time, issue.created + issue.admission_time) - now()")

238 end

239 }

240

241 filter[#filter+1] = {

242 name = "latest",

243 label = _"Latest",

244 selector_modifier = function(selector)

245 selector:add_order_by("issue.created DESC")

246 end

247 }

248

249 filter[#filter+1] = {

250 name = "max_potential_support",

251 label = _"Supporter count",

252 selector_modifier = function(selector)

253 selector:add_order_by("(SELECT max(supporter_count) FROM initiative WHERE initiative.issue_id = issue.id) DESC")

254 end

255 }

256

257 filters[#filters+1] = filter

258

259 end

260 --]]

261

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

263 filters[#filters+1] = {

264 name = "filter_voting",

265 {

266 name = "any",

267 label = _"Any",

268 selector_modifier = function() end

269 },

270 {

271 name = "not_voted",

272 label = _"Not voted",

273 selector_modifier = function(selector)

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

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

276 end

277 },

278 {

279 name = "voted",

280 label = _"Voted",

281 selector_modifier = function(selector)

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

283 end

284 },

285 }

286 end

287

288

289

290

291 function filters:get_filter(group, name)

292 for i,grp in ipairs(self) do

293 if grp.name == group then

294 for i,entry in ipairs(grp) do

295 if entry.name == name then

296 return entry

297 end

298 end

299 end

300 end

301 end

302

303 return filters