1 config = require('../config.js');

2 exports.config = config;

3

4 // ==========================================================================

5 // Handle generic request parameters

6 // --------------------------------------------------------------------------

7

8

9 exports.addLimitAndOffset = function (query, params) {

10 var limit = params.limit ? parseInt(params.limit) : config.settings.result_row_limit.default

11 if (limit > config.settings.result_row_limit.max) {

12 limit = config.settings.result_row_limit.default;

13 }

14 query.limit(limit);

15 query.offset(params.offset ? parseInt(params.offset) : 0);

16 }

17

18 // add member related options to a db query according to parameters given by client

19 // TODO limit to access privileges

20 exports.addMemberOptions = function (req, query, params, relation) {

21 var table_name = 'member';

22 if (relation) table_name = relation + '_member';

23

24 var member_id = params[relation ? relation + '_member_id' : 'member_id'];

25 var member_active = params[relation ? relation + '_member_active' : 'member_active'] ? true : false;

26 var member_search = params[relation ? relation + '_member_search' : 'member_search'];

27 var member_order_by_name = parseInt(params[relation ? relation + '_member_order_by_name' : 'member_order_by_name']);

28 var member_order_by_created = parseInt(params[relation ? relation + '_member_order_by_created' : 'member_order_by_created']);

29

30 if (member_id) {

31 query.addWhere(['"' + table_name + '"."id" IN (??)', member_id.split(',')]);

32 };

33 if (member_active === '1') {

34 query.addWhere('"' + table_name + '"."active" = TRUE OR "' + table_name + '"."active" ISNULL');

35 } else if (member_active === '0') {

36 query.addWhere('"' + table_name + '"."active" = FALSE');

37 };

38 if (member_search) {

39 query.addWhere(['"' + table_name + '"."text_search_data" @@ text_search_query(?)', member_search]);

40 };

41 if (member_order_by_name) {

42 query.addOrderBy('"' + table_name + '"."name"')

43 }

44 if (member_order_by_created) {

45 query.addOrderBy('"' + table_name + '"."created" DESC')

46 }

47 };

48

49 // add policy related options to a db query according to parameters given by client

50 exports.addPolicyOptions = function (req, query, params) {

51 if (params.policy_id) {

52 query.addWhere(['policy.id IN (??)', params.policy_id.split(',')]);

53 }

54 if (params.policy_order_by_name) {

55 query.addOrderBy('"policy"."name"')

56 }

57 };

58

59 // add unit related options to a db query according to parameters given by client

60 exports.addUnitOptions = function (req, query, params) {

61 if (params.unit_id) {

62 query.addWhere(['"unit"."id" IN (??)', params.unit_id.split(',')]);

63 }

64 if (params.unit_parent_id) {

65 query.addWhere(['"unit"."parent_id" = ?', params.unit_parent_id]);

66 }

67 if (params.unit_without_parent === '1') {

68 query.addWhere('"unit"."parent_id" ISNULL');

69 }

70 if (params.unit_disabled == 'only') {

71 query.addWhere('"unit"."active" = FALSE');

72 } else if (params.unit_disabled != 'include') {

73 query.addWhere('"unit"."active" = TRUE');

74 }

75 if (params.unit_order_by_name) {

76 query.addOrderBy('"unit"."name"')

77 }

78 }

79

80 // add area related options to a db query according to parameters given by client

81 exports.addAreaOptions = function (req, query, params) {

82 exports.addUnitOptions(req, query, params);

83 if (params.area_id) {

84 query.addWhere(['"area"."id" IN (??)', params.area_id.split(',')]);

85 }

86 if (params.area_disabled == 'only') {

87 query.addWhere('"area"."active" = FALSE');

88 } else if (params.area_disabled == 'include') {

89 query.addWhere('"area"."active" = TRUE');

90 }

91 if (req.current_access_level == 'member' && params.area_my) {

92 query.addWhere(['"area"."id" IN (SELECT "area_id" FROM "membership" WHERE "member_id" = ?)', req.current_member_id]);

93 }

94 if (params.area_order_by_name) {

95 query.addOrderBy('"area"."name"')

96 }

97 }

98

99 // add issue related options to a db query according to parameters given by client

100 exports.addIssueOptions = function (req, query, params) {

101 exports.addAreaOptions(req, query, params);

102 exports.addPolicyOptions(req, query, params);

103

104 if (params.issue_id) query.addWhere(['issue.id IN (??)', params.issue_id.split(',')]);

105

106 if (params.issue_state) {

107 var issue_state_string;

108 if (params.issue_state == 'open') {

109 issue_states = ['admission', 'discussion', 'verification', 'voting'];

110 } else if (params.issue_state == 'closed') {

111 issue_state_string = ['canceled_revoked_before_accepted', 'canceled_issue_not_accepted', 'canceled_after_revocation_during_discussion', 'canceled_after_revocation_during_verification', 'calculation', 'canceled_no_initiative_admitted', 'finished_without_winner', 'finished_with_winner'];

112 } else {

113 issue_states = params.issue_state.split(',');

114 }

115 query.addWhere(['"issue"."state" IN (??)', issue_states]);

116 };

117

118 if (params.issue_accepted === '1') query.addWhere('"issue"."accepted" NOTNULL');

119 if (params.issue_accepted === '0') query.addWhere('"issue"."accepted" ISNULL');

120 if (params.issue_half_frozen === '1') query.addWhere('"issue"."half_frozen" NOTNULL');

121 if (params.issue_half_frozen === '0') query.addWhere('"issue"."half_frozen" ISNULL');

122 if (params.issue_fully_frozen === '1') query.addWhere('"issue"."fully_frozen" NOTNULL');

123 if (params.issue_fully_frozen === '0') query.addWhere('"issue"."fully_frozen" ISNULL');

124 if (params.issue_closed === '1') query.addWhere('"issue"."closed" NOTNULL');

125 if (params.issue_closed === '0') query.addWhere('"issue"."closed" ISNULL');

126 if (params.issue_cleaned === '1') query.addWhere('"issue"."cleaned" NOTNULL');

127 if (params.issue_cleaned === '0') query.addWhere('"issue"."cleaned" ISNULL');

128

129 if (params.issue_created_after) query.addWhere(['"issue"."created" >= ?', params.issue_created_after]);

130 if (params.issue_created_before) query.addWhere(['"issue"."created" < ?', params.issue_created_before]);

131 if (params.issue_accepted_after) query.addWhere(['"issue"."accepted" >= ?', params.issue_accepted_after]);

132 if (params.issue_accepted_before) query.addWhere(['"issue"."accepted" < ?', params.issue_accepted_before]);

133 if (params.issue_half_frozen_after) query.addWhere(['"issue"."half_frozen" >= ?', params.issue_half_frozen_after]);

134 if (params.issue_half_frozen_before) query.addWhere(['"issue"."half_frozen" < ?', params.issue_half_frozen_before]);

135 if (params.issue_fully_frozen_after) query.addWhere(['"issue"."fully_frozen" >= ?', params.issue_fully_frozen_after]);

136 if (params.issue_fully_frozen_before) query.addWhere(['"issue"."fully_frozen" < ?', params.issue_fully_frozen_before]);

137 if (params.issue_closed_after) query.addWhere(['"issue"."closed" >= ?', params.issue_closed_after]);

138 if (params.issue_closed_before) query.addWhere(['"issue"."closed" < ?', params.issue_closed_before]);

139 if (params.issue_cleaned_after) query.addWhere(['"issue."cleaned" >= ?', params.issue_cleaned_after]);

140 if (params.issue_cleaned_before) query.addWhere(['"issue"."cleaned" < ?', params.issue_cleaned_before]);

141

142 if (params.issue_state_time_left_below) {

143 query.addWhere(['(case issue.state when \'admission\' then issue.created + issue.admission_time when \'discussion\' then issue.accepted + issue.discussion_time when \'verification\' then issue.half_frozen + issue.verification_time when \'voting\' then issue.fully_frozen + issue.voting_time end) - now() < ?', params.issue_state_time_left_below]);

144 };

145

146 if (params.issue_order_by_state_time_left) {

147 query.addOrderBy('(case issue.state when \'admission\' then issue.created + issue.admission_time when \'discussion\' then issue.accepted + issue.discussion_time when \'verification\' then issue.half_frozen + issue.verification_time when \'voting\' then issue.fully_frozen + issue.voting_time end)');

148 };

149

150 if (params.issue_order_by_id) query.addOrderBy('"issue"."id"');

151 }

152

153 // add initiative related options to a db query according to parameters given by client

154 exports.addInitiativeOptions = function (req, query, params) {

155 exports.addIssueOptions(req, query, params);

156 if (params.initiative_id) {

157 query.addWhere(['initiative.id IN (??)', params.initiative_id.split(',')]);

158 }

159 // TODO

160 //query.from.push('JOIN initiator AS initiative_initiator ON initiative_initiator.initiative_id = initiative.id AND initiative_initiator.accepted JOIN member AS initiator_member ON initiator_member.id = initiative_initiator.member_id');

161 //query.from.push('JOIN supporter AS initiative_supporter ON initiative_supporter.initiative_id = initiative.id JOIN member AS supporter_member ON supporter_member.id = initiative_supporter.member_id');

162 //exports.addMemberOptions(query, params, 'initiator');

163 //exports.addMemberOptions(query, params, 'supporter');

164

165 if (params.initiative_revoked === '1') query.addWhere('initiative.revoked NOTNULL');

166 if (params.initiative_revoked === '0') query.addWhere('initiative.revoked ISNULL');

167 if (params.initiative_revoked_after) query.addWhere(['initiative.revoked >= ?', params.initiative_revoked_after]);

168 if (params.initiative_revoked_before) query.addWhere(['initiative.revoked < ?', params.initiative_revoked_before]);

169 // TODO check accesslevel

170 if (params.initiative_revoked_by_member_id) query.addWhere(['initiative.revoked_by_member_id = ?', params.initiative_revoked_by_member_id]);

171 if (params.initiative_suggested_initiative_id) query.addWhere(['initiative.suggested_initiative_id = ?', params.initiative_suggested_initiative_id]);

172

173 if (params.initiative_admitted === '1') query.addWhere('initiative.admitted NOTNULL');

174 if (params.initiative_admitted === '0') query.addWhere('initiative.admitted ISNULL');

175 if (params.initiative_created_after) query.addWhere(['initiative.created >= ?', params.initiative_created_after]);

176 if (params.initiative_created_before) query.addWhere(['initiative.created < ?',params.initiative_created_before]);

177 if (params.initiative_admitted_after) query.addWhere(['initiative.admitted >= ?', params.initiative_admitted_after]);

178 if (params.initiative_admitted_before) query.addWhere(['initiative.admitted < ?', params.initiative_admitted_before]);

179

180 if (params.initiative_supporter_count_below) query.addWhere(['initiative.supporter_count < ?', params.initiative_supporter_count_below]);

181 if (params.initiative_supporter_count_above) query.addWhere(['initiative.supporter_count >= ?', params.initiative_supporter_count_above]);

182

183 if (params.initiative_attainable === '1') query.addWhere('initiative.attainable');

184 if (params.initiative_attainable === '0') query.addWhere('NOT initiative.attainable');

185 if (params.initiative_favored === '1') query.addWhere('initiative.favored');

186 if (params.initiative_favored === '0') query.addWhere('NOT initiative.favored');

187 if (params.initiative_unfavored === '1') query.addWhere('initiative.unfavored');

188 if (params.initiative_unfavored === '0') query.addWhere('NOT initiative.unfavored');

189

190 if (params.initiative_max_preliminary_rank) query.addWhere(['initiative.preliminary_rank <= ?', params.initiative_max_preliminary_rank]);

191 if (params.initiative_max_final_rank) query.addWhere(['initiative.preliminary_rank <= ?', params.initiative_max_final_rank]);

192

193 if (params.initiative_disqualified === '1') query.addWhere('initiative.disqualified');

194 if (params.initiative_disqualified === '0') query.addWhere('NOT initiative.disqualified');

195 if (params.initiative_winner === '1') query.addWhere('initiative.winner');

196 if (params.initiative_winner === '0') query.addWhere('NOT initiative.winner');

197

198 if (params.initiative_search) {

199 query.addWhere(['initiative.text_search_data @@ text_search_query(?)', params.initiative_search]);

200 };

201 if (params.initiative_order_by_id) {

202 query.addOrderBy('initiative.id');

203 }

204 }

205

206 // add suggestion related options to a db query according to parameters given by client

207 exports.addSuggestionOptions = function (req, query, params) {

208 exports.addInitiativeOptions(req, query, params);

209 if (params.suggestion_id) {

210 query.addWhere(['suggestion.id IN (??)', params.suggestion_id.split(',')]);

211 }

212 if (params.suggestion_search) {

213 query.addWhere(['suggestion.text_search_data @@ text_search_query(?)', params.suggestion_search]);

214 };

215 if (params.include_suggestion) {

216 addObjectFields(query, 'suggestion');

217 };

218 }