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) {

23 table_name = relation + '_member';

24 };

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

26 var member_disabled = params[relation ? relation + '_member_disabled' : 'member_disabled'] ? true : false;

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

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

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

30

31 if (member_id) {

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

33 };

34 /*if (member_disabled == true) {

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

36 } else {

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

38 };*/

39 if (member_search) {

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

41 };

42 if (member_order_by_name) {

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

44 }

45 if (member_order_by_created) {

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

47 }

48 };

49

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

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

52 if (params.policy_id) {

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

54 }

55 if (params.policy_order_by_name) {

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

57 }

58 };

59

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

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

62 if (params.unit_id) {

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

64 }

65 if (params.unit_parent_id) {

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

67 }

68 if (params.unit_without_parent) {

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

70 }

71 if (params.unit_disabled) {

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

73 } else {

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

75 }

76 if (params.unit_order_by_name) {

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

78 }

79 }

80

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

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

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

84 if (params.area_id) {

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

86 }

87 if (params.area_disabled) {

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

89 } else {

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

91 }

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

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

94 }

95 if (params.area_order_by_name) {

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

97 }

98 }

99

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

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

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

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

104

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

106

107 if (params.issue_state) {

108 var issue_state_string;

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

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

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

112 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'];

113 } else {

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

115 }

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

117 };

118

119 if (params.issue_accepted) query.addWhere('"issue"."accepted" NOTNULL');

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

121 if (params.issue_fully_frozen) query.addWhere('"issue"."fully_frozen" NOTNULL');

122 if (params.issue_closed) query.addWhere('"issue"."closed" NOTNULL');

123 if (params.issue_cleaned) query.addWhere('"issue"."cleaned" NOTNULL');

124

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

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

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

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

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

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

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

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

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

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

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

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

137

138 if (params.issue_state_time_left_below) {

139 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]);

140 };

141

142 if (params.issue_order_by_state_time_left) {

143 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)');

144 };

145

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

147 }

148

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

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

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

152 if (params.initiative_id) {

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

154 }

155 // TODO

156 //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');

157 //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');

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

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

160

161 if (params.initiative_revoked) query.addWhere('initiative.revoked NOTNULL');

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

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

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

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

166

167 if (params.initiative_admitted) query.addWhere('initiative.admitted NOTNULL');

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

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

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

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

172

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

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

175

176 if (params.initiative_attainable) query.addWhere('initiative.attainable');

177 if (params.initiative_favored) query.addWhere('initiative.favored');

178 if (params.initiative_unfavored) query.addWhere('initiative.unfavored');

179

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

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

182

183 if (params.initiative_disqualified) query.addWhere('initiative.disqualified');

184 if (params.initiative_winner) query.addWhere('initiative.winner');

185

186 if (params.initiative_search) {

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

188 };

189 if (params.initiative_order_by_id) {

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

191 }

192 }

193

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

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

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

197 if (params.suggestion_id) {

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

199 }

200 if (params.suggestion_search) {

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

202 };

203 if (params.include_suggestion) {

204 addObjectFields(query, 'suggestion');

205 };

206 }