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 = '1m',

14 to = "Draft",

15 this_key = 'id',

16 that_key = 'initiative_id',

17 ref = 'drafts',

18 back_ref = 'initiative',

19 default_order = '"id" DESC'

20 }

21

22 Initiative:add_reference{

23 mode = '1m',

24 to = "Suggestion",

25 this_key = 'id',

26 that_key = 'initiative_id',

27 ref = 'suggestions',

28 back_ref = 'initiative',

29 }

30

31 Initiative:add_reference{

32 mode = '1m',

33 to = "Initiator",

34 this_key = 'id',

35 that_key = 'initiative_id',

36 ref = 'initiators',

37 back_ref = 'initiative'

38 }

39

40 Initiative:add_reference{

41 mode = '1m',

42 to = "Supporter",

43 this_key = 'id',

44 that_key = 'initiative_id',

45 ref = 'supporters',

46 back_ref = 'initiative',

47 default_order = '"id"'

48 }

49

50 Initiative:add_reference{

51 mode = '1m',

52 to = "Opinion",

53 this_key = 'id',

54 that_key = 'initiative_id',

55 ref = 'opinions',

56 back_ref = 'initiative',

57 default_order = '"id"'

58 }

59

60 Initiative:add_reference{

61 mode = '1m',

62 to = "Vote",

63 this_key = 'id',

64 that_key = 'initiative_id',

65 ref = 'votes',

66 back_ref = 'initiative',

67 default_order = '"member_id"'

68 }

69

70 Initiative:add_reference{

71 mode = 'm1',

72 to = "Initiative",

73 this_key = 'suggested_initiative_id',

74 that_key = 'id',

75 ref = 'suggested_initiative',

76 }

77

78 Initiative:add_reference{

79 mode = 'mm',

80 to = "Member",

81 this_key = 'id',

82 that_key = 'id',

83 connected_by_table = '"initiator"',

84 connected_by_this_key = 'initiative_id',

85 connected_by_that_key = 'member_id',

86 ref = 'initiating_members'

87 }

88

89 Initiative:add_reference{

90 mode = 'mm',

91 to = "Member",

92 this_key = 'id',

93 that_key = 'id',

94 connected_by_table = '"supporter"',

95 connected_by_this_key = 'initiative_id',

96 connected_by_that_key = 'member_id',

97 ref = 'supporting_members'

98 }

99

100 Initiative:add_reference{

101 mode = 'mm',

102 to = "Member",

103 this_key = 'id',

104 that_key = 'id',

105 connected_by_table = 'direct_supporter_snapshot',

106 connected_by_this_key = 'initiative_id',

107 connected_by_that_key = 'member_id',

108 ref = 'supporting_members_snapshot'

109 }

110

111 Initiative:add_reference{

112 mode = "11",

113 to = mondelefant.class_prototype,

114 this_key = "id",

115 that_key = "initiative_id",

116 ref = "member_info",

117 back_ref = "initiative",

118 selector_generator = function(list, options)

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

120 local ids = { sep = ", " }

121 local issue_ids = { sep = ", " }

122 for i, object in ipairs(list) do

123 local id = object.id

124 if id ~= nil then

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

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

127 end

128 end

129

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

131 if #ids == 0 then

132 return sub_selector:empty_list_mode()

133 end

134 sub_selector:from("issue")

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

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

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

138

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

140 selector:add_from("initiative")

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

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

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

144 selector:add_field("delegation_info.*")

145

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

147

148 selector:left_join("delegating_interest_snapshot", "delegating_interest_s", { "delegating_interest_s.event = issue.latest_snapshot_event AND delegating_interest_s.issue_id = issue.id AND delegating_interest_s.member_id = ?", options.member_id })

149

150 selector:left_join("direct_supporter_snapshot", "supporter_s", { "supporter_s.event = issue.latest_snapshot_event 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 })

151

152 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")

153 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")

154

155 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")

156

157

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

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

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

161

162 return selector

163 end

164 }

165

166 function Initiative.list:load_everything_for_member_id(member_id)

167 if member_id then

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

169 end

170 end

171

172 function Initiative.object:load_everything_for_member_id(member_id)

173 if member_id then

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

175 end

176 end

177

178

179

180

181 function Initiative:get_search_selector(search_string)

182 return self:new_selector()

183 :join("draft", nil, "draft.initiative_id = initiative.id")

184 :add_field( {'"highlight"("initiative"."name", ?)', search_string }, "name_highlighted")

185 :add_where{ '"initiative"."text_search_data" @@ "text_search_query"(?) OR "draft"."text_search_data" @@ "text_search_query"(?)', search_string, search_string }

186 :add_group_by('"initiative"."id"')

187 :add_group_by('"initiative"."issue_id"')

188 :add_group_by('"initiative"."name"')

189 :add_group_by('"initiative"."discussion_url"')

190 :add_group_by('"initiative"."created"')

191 :add_group_by('"initiative"."revoked"')

192 :add_group_by('"initiative"."revoked_by_member_id"')

193 :add_group_by('"initiative"."admitted"')

194 :add_group_by('"initiative"."supporter_count"')

195 :add_group_by('"initiative"."informed_supporter_count"')

196 :add_group_by('"initiative"."satisfied_supporter_count"')

197 :add_group_by('"initiative"."satisfied_informed_supporter_count"')

198 :add_group_by('"initiative"."positive_votes"')

199 :add_group_by('"initiative"."negative_votes"')

200 :add_group_by('"initiative"."direct_majority"')

201 :add_group_by('"initiative"."indirect_majority"')

202 :add_group_by('"initiative"."schulze_rank"')

203 :add_group_by('"initiative"."better_than_status_quo"')

204 :add_group_by('"initiative"."worse_than_status_quo"')

205 :add_group_by('"initiative"."reverse_beat_path"')

206 :add_group_by('"initiative"."multistage_majority"')

207 :add_group_by('"initiative"."eligible"')

208 :add_group_by('"initiative"."winner"')

209 :add_group_by('"initiative"."rank"')

210 :add_group_by('"initiative"."suggested_initiative_id"')

211 :add_group_by('"initiative"."text_search_data"')

212 :add_group_by('"issue"."population"')

213 :add_group_by("_initiator.member_id")

214 :add_group_by("_supporter.member_id")

215 :add_group_by("_direct_supporter_snapshot.member_id")

216 end

217

218 function Initiative:selector_for_updated_drafts(member_id)

219 return Initiative:new_selector()

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

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

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

223 :add_where("initiative.revoked ISNULL")

224 end

225

226

227 function Initiative.object_get:current_draft()

228 return Draft:new_selector()

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

230 :add_order_by('"id" DESC')

231 :single_object_mode()

232 :exec()

233 end

234

235 function Initiative.object_get:shortened_name()

236 local name = self.name

237 if #name > 100 then

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

239 end

240 return name

241 end

242

243 function Initiative.object_get:initiator_names()

244 local members = Member:new_selector()

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

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

247 :add_where{ "initiator.accepted" }

248 :exec()

249

250 local member_names = {}

251 for i, member in ipairs(members) do

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

253 end

254 return member_names

255 end

256