1 Initiative = mondelefant.new_class()

2 Initiative.table = 'initiative'

3

4 Initiative:add_reference{

5 mode = 'm1',

6 to = "Issue",

7 this_key = 'issue_id',

8 that_key = 'id',

9 ref = 'issue',

10 }

11

12 Initiative:add_reference{

13 mode = 'm1',

14 to = "Member",

15 this_key = 'revoked_by_member_id',

16 that_key = 'id',

17 ref = 'revoked_by_member',

18 }

19

20 Initiative:add_reference{

21 mode = '1m',

22 to = "Draft",

23 this_key = 'id',

24 that_key = 'initiative_id',

25 ref = 'drafts',

26 back_ref = 'initiative',

27 default_order = '"id" DESC'

28 }

29

30 Initiative:add_reference{

31 mode = '1m',

32 to = "Suggestion",

33 this_key = 'id',

34 that_key = 'initiative_id',

35 ref = 'suggestions',

36 back_ref = 'initiative',

37 default_order = '"proportional_order" NULLS LAST, id'

38 }

39

40 Initiative:add_reference{

41 mode = '1m',

42 to = "Initiator",

43 this_key = 'id',

44 that_key = 'initiative_id',

45 ref = 'initiators',

46 back_ref = 'initiative'

47 }

48

49 Initiative:add_reference{

50 mode = '1m',

51 to = "Supporter",

52 this_key = 'id',

53 that_key = 'initiative_id',

54 ref = 'supporters',

55 back_ref = 'initiative',

56 default_order = '"id"'

57 }

58

59 Initiative:add_reference{

60 mode = '1m',

61 to = "Opinion",

62 this_key = 'id',

63 that_key = 'initiative_id',

64 ref = 'opinions',

65 back_ref = 'initiative',

66 default_order = '"id"'

67 }

68

69 Initiative:add_reference{

70 mode = '1m',

71 to = "Vote",

72 this_key = 'id',

73 that_key = 'initiative_id',

74 ref = 'votes',

75 back_ref = 'initiative',

76 default_order = '"member_id"'

77 }

78

79 Initiative:add_reference{

80 mode = 'm1',

81 to = "Initiative",

82 this_key = 'suggested_initiative_id',

83 that_key = 'id',

84 ref = 'suggested_initiative',

85 }

86

87 Initiative:add_reference{

88 mode = 'mm',

89 to = "Member",

90 this_key = 'id',

91 that_key = 'id',

92 connected_by_table = '"initiator"',

93 connected_by_this_key = 'initiative_id',

94 connected_by_that_key = 'member_id',

95 ref = 'initiating_members'

96 }

97

98 Initiative:add_reference{

99 mode = 'mm',

100 to = "Member",

101 this_key = 'id',

102 that_key = 'id',

103 connected_by_table = '"supporter"',

104 connected_by_this_key = 'initiative_id',

105 connected_by_that_key = 'member_id',

106 ref = 'supporting_members'

107 }

108

109 Initiative:add_reference{

110 mode = 'mm',

111 to = "Member",

112 this_key = 'id',

113 that_key = 'id',

114 connected_by_table = 'direct_supporter_snapshot',

115 connected_by_this_key = 'initiative_id',

116 connected_by_that_key = 'member_id',

117 ref = 'supporting_members_snapshot'

118 }

119

120 Initiative:add_reference{

121 mode = "11",

122 to = mondelefant.class_prototype,

123 this_key = "id",

124 that_key = "initiative_id",

125 ref = "member_info",

126 back_ref = "initiative",

127 selector_generator = function(list, options)

128 assert(options.member_id, "member_id mandatory for member_info")

129 local ids = { sep = ", " }

130 local issue_ids = { sep = ", " }

131 for i, object in ipairs(list) do

132 local id = object.id

133 if id ~= nil then

134 ids[#ids+1] = {"?", id}

135 issue_ids[#issue_ids+1] = { "?", object.issue_id }

136 end

137 end

138

139 local sub_selector = Issue:get_db_conn():new_selector()

140 if #ids == 0 then

141 return sub_selector:empty_list_mode()

142 end

143 sub_selector:from("issue")

144 sub_selector:add_field("issue.id", "issue_id")

145 sub_selector:add_field{ '(delegation_info(?, null, null, issue.id, ?)).*', options.member_id, options.trustee_id }

146 sub_selector:add_where{ 'issue.id IN ($)', issue_ids }

147

148 local selector = Initiative:get_db_conn():new_selector()

149 selector:add_from("initiative")

150 selector:add_field("initiative.id", "initiative_id")

151 selector:join("issue", nil, "issue.id = initiative.issue_id")

152 selector:join(sub_selector, "delegation_info", "delegation_info.issue_id = issue.id")

153 selector:add_field("delegation_info.*")

154

155 selector:left_join("supporter", nil, "supporter.initiative_id = initiative.id AND supporter.member_id = delegation_info.participating_member_id")

156

157 selector:left_join("delegating_interest_snapshot", "delegating_interest_s", { "delegating_interest_s.snapshot_id = issue.latest_snapshot_id AND delegating_interest_s.issue_id = issue.id AND delegating_interest_s.member_id = ?", options.member_id })

158

159 selector:left_join("direct_supporter_snapshot", "supporter_s", { "supporter_s.snapshot_id = issue.latest_snapshot_id AND supporter_s.initiative_id = initiative.id AND (supporter_s.member_id = ? OR supporter_s.member_id = delegating_interest_s.delegate_member_ids[array_upper(delegating_interest_s.delegate_member_ids, 1)])", options.member_id })

160

161 selector:add_field("CASE WHEN issue.fully_frozen ISNULL AND issue.closed ISNULL THEN supporter.member_id NOTNULL ELSE supporter_s.member_id NOTNULL END", "supported")

162 selector:add_field({ "CASE WHEN issue.fully_frozen ISNULL AND issue.closed ISNULL THEN delegation_info.own_participation AND supporter.member_id NOTNULL ELSE supporter_s.member_id = ? END", options.member_id }, "directly_supported")

163

164 selector:add_field("CASE WHEN issue.fully_frozen ISNULL AND issue.closed ISNULL THEN supporter.member_id NOTNULL AND NOT EXISTS(SELECT 1 FROM critical_opinion WHERE critical_opinion.initiative_id = initiative.id AND critical_opinion.member_id = delegation_info.participating_member_id) ELSE supporter_s.satisfied NOTNULL END", "satisfied")

165

166

167 --selector:add_field("", "informed")

168 selector:left_join("initiator", nil, { "initiator.initiative_id = initiative.id AND initiator.member_id = ? AND initiator.accepted", options.member_id })

169 selector:add_field("initiator.member_id NOTNULL", "initiated")

170

171 return selector

172 end

173 }

174

175 function Initiative.list:load_everything_for_member_id(member_id)

176 if member_id then

177 self:load("member_info", { member_id = member_id })

178 end

179 end

180

181 function Initiative.object:load_everything_for_member_id(member_id)

182 if member_id then

183 self:load("member_info", { member_id = member_id })

184 end

185 end

186

187 function Initiative:selector_for_updated_drafts(member_id)

188 return Initiative:new_selector()

189 :join("issue", "_issue_state", "_issue_state.id = initiative.issue_id AND _issue_state.closed ISNULL AND _issue_state.fully_frozen ISNULL")

190 :join("current_draft", "_current_draft", "_current_draft.initiative_id = initiative.id")

191 :join("supporter", "supporter", { "supporter.member_id = ? AND supporter.initiative_id = initiative.id AND supporter.draft_id < _current_draft.id", member_id })

192 :add_where("initiative.revoked ISNULL")

193 end

194

195 function Initiative:getSpecialSelector( args )

196 local selector = Initiative:new_selector()

197 selector:join( "issue", nil, "issue.id = initiative.issue_id" )

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

199 if args.area_id then

200 selector:add_where{ "area.id = ?", args.area_id }

201 elseif args.unit_id then

202 selector:add_where{ "area.unit_id = ?", args.unit_id }

203 end

204 selector:limit( 1 )

205 selector:optional_object_mode()

206 return selector

207 end

208

209 function Initiative:getLastWinner( args )

210 local selector = Initiative:getSpecialSelector( args )

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

212 selector:add_order_by( "issue.closed DESC, id DESC" )

213 return selector:exec()

214 end

215

216 function Initiative:getLastLoser( args )

217 local selector = Initiative:getSpecialSelector( args )

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

219 selector:add_order_by( "issue.closed DESC, id DESC" )

220 return selector:exec()

221 end

222

223 function Initiative:getNextEndingVoting( args )

224 local selector = Initiative:getSpecialSelector( args )

225 selector:add_where( "issue.state = 'voting'" )

226 selector:add_order_by( "issue.fully_frozen + issue.verification_time DESC, id DESC" )

227 return selector:exec()

228 end

229

230 function Initiative:getNextEndingVerification( args )

231 local selector = Initiative:getSpecialSelector( args )

232 selector:add_where( "issue.state = 'verification'" )

233 selector:add_order_by( "issue.half_frozen + issue.verification_time DESC, id DESC" )

234 return selector:exec()

235 end

236

237 function Initiative:getNextEndingDiscussion( args )

238 local selector = Initiative:getSpecialSelector( args )

239 selector:add_where( "issue.state = 'discussion'" )

240 selector:add_order_by( "issue.accepted + issue.discussion_time DESC, id DESC" )

241 return selector:exec()

242 end

243

244 function Initiative:getBestInAdmission( args )

245 local selector = Initiative:getSpecialSelector( args )

246 selector:add_where( "issue.state = 'admission'" )

247 selector:add_order_by( "issue.created + issue.max_admission_time DESC, id DESC" )

248 return selector:exec()

249 end

250

251 function Initiative.object_get:current_draft()

252 return Draft:new_selector()

253 :add_where{ '"initiative_id" = ?', self.id }

254 :add_order_by('"id" DESC')

255 :single_object_mode()

256 :exec()

257 end

258

259 function Initiative.object_get:shortened_name()

260 local name = self.name

261 if #name > 100 then

262 name = name:sub(1,100) .. "..."

263 end

264 return name

265 end

266

267 function Initiative.object_get:display_name()

268 return "i" .. self.id .. ": " .. self.name

269 end

270

271 function Initiative.object_get:initiator_names()

272 local members = Member:new_selector()

273 :join("initiator", nil, "initiator.member_id = member.id")

274 :add_where{ "initiator.initiative_id = ?", self.id }

275 :add_where{ "initiator.accepted" }

276 :exec()

277

278 local member_names = {}

279 for i, member in ipairs(members) do

280 member_names[#member_names+1] = member.name

281 end

282 return member_names

283 end

284

285 function Initiative.object_get:potential_supporter_count()

286 if self.supporter_count and self.satisfied_supporter_count then

287 return self.supporter_count - self.satisfied_supporter_count

288 end

289 end