| rev |
line source |
|
bsw@0
|
1 config = require('../config.js');
|
|
bsw@0
|
2 exports.config = config;
|
|
bsw@0
|
3
|
|
bsw@0
|
4 // ==========================================================================
|
|
bsw@0
|
5 // Handle generic request parameters
|
|
bsw@0
|
6 // --------------------------------------------------------------------------
|
|
bsw@0
|
7
|
|
bsw@0
|
8
|
|
bsw@0
|
9 exports.addLimitAndOffset = function (query, params) {
|
|
bsw@0
|
10 var limit = params.limit ? parseInt(params.limit) : config.settings.result_row_limit.default
|
|
bsw@0
|
11 if (limit > config.settings.result_row_limit.max) {
|
|
bsw@0
|
12 limit = config.settings.result_row_limit.default;
|
|
bsw@0
|
13 }
|
|
bsw@0
|
14 query.limit(limit);
|
|
bsw@0
|
15 query.offset(params.offset ? parseInt(params.offset) : 0);
|
|
bsw@0
|
16 }
|
|
bsw@0
|
17
|
|
bsw@0
|
18 // add member related options to a db query according to parameters given by client
|
|
bsw@16
|
19 // TODO limit to access privileges
|
|
bsw@0
|
20 exports.addMemberOptions = function (req, query, params, relation) {
|
|
bsw@0
|
21 var table_name = 'member';
|
|
bsw@20
|
22 if (relation) table_name = relation + '_member';
|
|
bsw@20
|
23
|
|
bsw@0
|
24 var member_id = params[relation ? relation + '_member_id' : 'member_id'];
|
|
bsw@24
|
25 var member_active = params[relation ? relation + '_member_active' : 'member_active'] ? true : false;
|
|
bsw@0
|
26 var member_search = params[relation ? relation + '_member_search' : 'member_search'];
|
|
bsw@0
|
27 var member_order_by_name = parseInt(params[relation ? relation + '_member_order_by_name' : 'member_order_by_name']);
|
|
bsw@0
|
28 var member_order_by_created = parseInt(params[relation ? relation + '_member_order_by_created' : 'member_order_by_created']);
|
|
bsw@0
|
29
|
|
bsw@0
|
30 if (member_id) {
|
|
bsw@0
|
31 query.addWhere(['"' + table_name + '"."id" IN (??)', member_id.split(',')]);
|
|
bsw@0
|
32 };
|
|
bsw@23
|
33 if (member_active == '1') {
|
|
bsw@23
|
34 query.addWhere('"' + table_name + '"."active" = TRUE OR "' + table_name + '"."active" ISNULL');
|
|
bsw@23
|
35 } else if (member_active == '0') {
|
|
bsw@0
|
36 query.addWhere('"' + table_name + '"."active" = FALSE');
|
|
bsw@20
|
37 };
|
|
bsw@0
|
38 if (member_search) {
|
|
bsw@0
|
39 query.addWhere(['"' + table_name + '"."text_search_data" @@ text_search_query(?)', member_search]);
|
|
bsw@0
|
40 };
|
|
bsw@0
|
41 if (member_order_by_name) {
|
|
bsw@0
|
42 query.addOrderBy('"' + table_name + '"."name"')
|
|
bsw@0
|
43 }
|
|
bsw@0
|
44 if (member_order_by_created) {
|
|
bsw@0
|
45 query.addOrderBy('"' + table_name + '"."created" DESC')
|
|
bsw@0
|
46 }
|
|
bsw@0
|
47 };
|
|
bsw@0
|
48
|
|
bsw@0
|
49 // add policy related options to a db query according to parameters given by client
|
|
bsw@0
|
50 exports.addPolicyOptions = function (req, query, params) {
|
|
bsw@0
|
51 if (params.policy_id) {
|
|
bsw@0
|
52 query.addWhere(['policy.id IN (??)', params.policy_id.split(',')]);
|
|
bsw@0
|
53 }
|
|
bsw@0
|
54 if (params.policy_order_by_name) {
|
|
bsw@0
|
55 query.addOrderBy('"policy"."name"')
|
|
bsw@0
|
56 }
|
|
bsw@0
|
57 };
|
|
bsw@0
|
58
|
|
bsw@0
|
59 // add unit related options to a db query according to parameters given by client
|
|
bsw@0
|
60 exports.addUnitOptions = function (req, query, params) {
|
|
bsw@0
|
61 if (params.unit_id) {
|
|
bsw@0
|
62 query.addWhere(['"unit"."id" IN (??)', params.unit_id.split(',')]);
|
|
bsw@0
|
63 }
|
|
bsw@0
|
64 if (params.unit_parent_id) {
|
|
bsw@0
|
65 query.addWhere(['"unit"."parent_id" = ?', params.unit_parent_id]);
|
|
bsw@0
|
66 }
|
|
bsw@20
|
67 if (params.unit_without_parent == '1') {
|
|
bsw@0
|
68 query.addWhere('"unit"."parent_id" ISNULL');
|
|
bsw@0
|
69 }
|
|
bsw@20
|
70 if (params.unit_disabled == 'only') {
|
|
bsw@0
|
71 query.addWhere('"unit"."active" = FALSE');
|
|
bsw@20
|
72 } else if (params.unit_disabled != 'include') {
|
|
bsw@0
|
73 query.addWhere('"unit"."active" = TRUE');
|
|
bsw@0
|
74 }
|
|
bsw@0
|
75 if (params.unit_order_by_name) {
|
|
bsw@0
|
76 query.addOrderBy('"unit"."name"')
|
|
bsw@0
|
77 }
|
|
bsw@0
|
78 }
|
|
bsw@0
|
79
|
|
bsw@0
|
80 // add area related options to a db query according to parameters given by client
|
|
bsw@0
|
81 exports.addAreaOptions = function (req, query, params) {
|
|
bsw@0
|
82 exports.addUnitOptions(req, query, params);
|
|
bsw@0
|
83 if (params.area_id) {
|
|
bsw@0
|
84 query.addWhere(['"area"."id" IN (??)', params.area_id.split(',')]);
|
|
bsw@0
|
85 }
|
|
bsw@20
|
86 if (params.area_disabled == 'only') {
|
|
bsw@0
|
87 query.addWhere('"area"."active" = FALSE');
|
|
bsw@20
|
88 } else if (params.area_disabled == 'include') {
|
|
bsw@0
|
89 query.addWhere('"area"."active" = TRUE');
|
|
bsw@0
|
90 }
|
|
bsw@0
|
91 if (req.current_access_level == 'member' && params.area_my) {
|
|
bsw@0
|
92 query.addWhere(['"area"."id" IN (SELECT "area_id" FROM "membership" WHERE "member_id" = ?)', req.current_member_id]);
|
|
bsw@0
|
93 }
|
|
bsw@0
|
94 if (params.area_order_by_name) {
|
|
bsw@0
|
95 query.addOrderBy('"area"."name"')
|
|
bsw@0
|
96 }
|
|
bsw@0
|
97 }
|
|
bsw@0
|
98
|
|
bsw@0
|
99 // add issue related options to a db query according to parameters given by client
|
|
bsw@0
|
100 exports.addIssueOptions = function (req, query, params) {
|
|
bsw@0
|
101 exports.addAreaOptions(req, query, params);
|
|
bsw@0
|
102 exports.addPolicyOptions(req, query, params);
|
|
bsw@0
|
103
|
|
bsw@0
|
104 if (params.issue_id) query.addWhere(['issue.id IN (??)', params.issue_id.split(',')]);
|
|
bsw@0
|
105
|
|
bsw@0
|
106 if (params.issue_state) {
|
|
bsw@0
|
107 var issue_state_string;
|
|
bsw@0
|
108 if (params.issue_state == 'open') {
|
|
bsw@0
|
109 issue_states = ['admission', 'discussion', 'verification', 'voting'];
|
|
bsw@0
|
110 } else if (params.issue_state == 'closed') {
|
|
bsw@0
|
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'];
|
|
bsw@0
|
112 } else {
|
|
bsw@0
|
113 issue_states = params.issue_state.split(',');
|
|
bsw@0
|
114 }
|
|
bsw@1
|
115 query.addWhere(['"issue"."state" IN (??)', issue_states]);
|
|
bsw@0
|
116 };
|
|
bsw@0
|
117
|
|
bsw@20
|
118 if (params.issue_accepted == '1') query.addWhere('"issue"."accepted" NOTNULL');
|
|
bsw@20
|
119 if (params.issue_accepted == '0') query.addWhere('"issue"."accepted" ISNULL');
|
|
bsw@20
|
120 if (params.issue_half_frozen == '1') query.addWhere('"issue"."half_frozen" NOTNULL');
|
|
bsw@20
|
121 if (params.issue_half_frozen == '0') query.addWhere('"issue"."half_frozen" ISNULL');
|
|
bsw@20
|
122 if (params.issue_fully_frozen == '1') query.addWhere('"issue"."fully_frozen" NOTNULL');
|
|
bsw@20
|
123 if (params.issue_fully_frozen == '0') query.addWhere('"issue"."fully_frozen" ISNULL');
|
|
bsw@20
|
124 if (params.issue_closed == '1') query.addWhere('"issue"."closed" NOTNULL');
|
|
bsw@20
|
125 if (params.issue_closed == '0') query.addWhere('"issue"."closed" ISNULL');
|
|
bsw@20
|
126 if (params.issue_cleaned == '1') query.addWhere('"issue"."cleaned" NOTNULL');
|
|
bsw@20
|
127 if (params.issue_cleaned == '0') query.addWhere('"issue"."cleaned" ISNULL');
|
|
bsw@20
|
128
|
|
bsw@20
|
129 if (params.issue_ranks_available == '1') query.addWhere('"issue"."ranks_available"');
|
|
bsw@20
|
130 if (params.issue_ranks_available == '0') query.addWhere('NOT "issue"."ranks_available"');
|
|
bsw@20
|
131
|
|
bsw@0
|
132 if (params.issue_created_after) query.addWhere(['"issue"."created" >= ?', params.issue_created_after]);
|
|
bsw@0
|
133 if (params.issue_created_before) query.addWhere(['"issue"."created" < ?', params.issue_created_before]);
|
|
bsw@0
|
134 if (params.issue_accepted_after) query.addWhere(['"issue"."accepted" >= ?', params.issue_accepted_after]);
|
|
bsw@0
|
135 if (params.issue_accepted_before) query.addWhere(['"issue"."accepted" < ?', params.issue_accepted_before]);
|
|
bsw@0
|
136 if (params.issue_half_frozen_after) query.addWhere(['"issue"."half_frozen" >= ?', params.issue_half_frozen_after]);
|
|
bsw@0
|
137 if (params.issue_half_frozen_before) query.addWhere(['"issue"."half_frozen" < ?', params.issue_half_frozen_before]);
|
|
bsw@0
|
138 if (params.issue_fully_frozen_after) query.addWhere(['"issue"."fully_frozen" >= ?', params.issue_fully_frozen_after]);
|
|
bsw@0
|
139 if (params.issue_fully_frozen_before) query.addWhere(['"issue"."fully_frozen" < ?', params.issue_fully_frozen_before]);
|
|
bsw@0
|
140 if (params.issue_closed_after) query.addWhere(['"issue"."closed" >= ?', params.issue_closed_after]);
|
|
bsw@0
|
141 if (params.issue_closed_before) query.addWhere(['"issue"."closed" < ?', params.issue_closed_before]);
|
|
bsw@0
|
142 if (params.issue_cleaned_after) query.addWhere(['"issue."cleaned" >= ?', params.issue_cleaned_after]);
|
|
bsw@0
|
143 if (params.issue_cleaned_before) query.addWhere(['"issue"."cleaned" < ?', params.issue_cleaned_before]);
|
|
bsw@0
|
144
|
|
bsw@0
|
145 if (params.issue_state_time_left_below) {
|
|
bsw@0
|
146 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]);
|
|
bsw@0
|
147 };
|
|
bsw@0
|
148
|
|
bsw@2
|
149 if (params.issue_order_by_state_time_left) {
|
|
bsw@2
|
150 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)');
|
|
bsw@2
|
151 };
|
|
bsw@2
|
152
|
|
bsw@0
|
153 if (params.issue_order_by_id) query.addOrderBy('"issue"."id"');
|
|
bsw@0
|
154 }
|
|
bsw@0
|
155
|
|
bsw@0
|
156 // add initiative related options to a db query according to parameters given by client
|
|
bsw@0
|
157 exports.addInitiativeOptions = function (req, query, params) {
|
|
bsw@0
|
158 exports.addIssueOptions(req, query, params);
|
|
bsw@0
|
159 if (params.initiative_id) {
|
|
bsw@0
|
160 query.addWhere(['initiative.id IN (??)', params.initiative_id.split(',')]);
|
|
bsw@0
|
161 }
|
|
bsw@0
|
162 // TODO
|
|
bsw@0
|
163 //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');
|
|
bsw@0
|
164 //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');
|
|
bsw@0
|
165 //exports.addMemberOptions(query, params, 'initiator');
|
|
bsw@0
|
166 //exports.addMemberOptions(query, params, 'supporter');
|
|
bsw@0
|
167
|
|
bsw@20
|
168 if (params.initiative_revoked == '1') query.addWhere('initiative.revoked NOTNULL');
|
|
bsw@20
|
169 if (params.initiative_revoked == '0') query.addWhere('initiative.revoked ISNULL');
|
|
bsw@0
|
170 if (params.initiative_revoked_after) query.addWhere(['initiative.revoked >= ?', params.initiative_revoked_after]);
|
|
bsw@0
|
171 if (params.initiative_revoked_before) query.addWhere(['initiative.revoked < ?', params.initiative_revoked_before]);
|
|
bsw@20
|
172 // TODO check accesslevel
|
|
bsw@0
|
173 if (params.initiative_revoked_by_member_id) query.addWhere(['initiative.revoked_by_member_id = ?', params.initiative_revoked_by_member_id]);
|
|
bsw@0
|
174 if (params.initiative_suggested_initiative_id) query.addWhere(['initiative.suggested_initiative_id = ?', params.initiative_suggested_initiative_id]);
|
|
bsw@0
|
175
|
|
bsw@20
|
176 if (params.initiative_admitted == '1') query.addWhere('initiative.admitted NOTNULL');
|
|
bsw@20
|
177 if (params.initiative_admitted == '0') query.addWhere('initiative.admitted ISNULL');
|
|
bsw@0
|
178 if (params.initiative_created_after) query.addWhere(['initiative.created >= ?', params.initiative_created_after]);
|
|
bsw@0
|
179 if (params.initiative_created_before) query.addWhere(['initiative.created < ?',params.initiative_created_before]);
|
|
bsw@0
|
180 if (params.initiative_admitted_after) query.addWhere(['initiative.admitted >= ?', params.initiative_admitted_after]);
|
|
bsw@0
|
181 if (params.initiative_admitted_before) query.addWhere(['initiative.admitted < ?', params.initiative_admitted_before]);
|
|
bsw@0
|
182
|
|
bsw@0
|
183 if (params.initiative_supporter_count_below) query.addWhere(['initiative.supporter_count < ?', params.initiative_supporter_count_below]);
|
|
bsw@0
|
184 if (params.initiative_supporter_count_above) query.addWhere(['initiative.supporter_count >= ?', params.initiative_supporter_count_above]);
|
|
bsw@0
|
185
|
|
bsw@20
|
186 if (params.initiative_attainable == '1') query.addWhere('initiative.attainable');
|
|
bsw@20
|
187 if (params.initiative_attainable == '0') query.addWhere('NOT initiative.attainable');
|
|
bsw@20
|
188 if (params.initiative_favored == '1') query.addWhere('initiative.favored');
|
|
bsw@20
|
189 if (params.initiative_favored == '0') query.addWhere('NOT initiative.favored');
|
|
bsw@20
|
190 if (params.initiative_unfavored == '1') query.addWhere('initiative.unfavored');
|
|
bsw@20
|
191 if (params.initiative_unfavored == '0') query.addWhere('NOT initiative.unfavored');
|
|
bsw@0
|
192
|
|
bsw@0
|
193 if (params.initiative_max_preliminary_rank) query.addWhere(['initiative.preliminary_rank <= ?', params.initiative_max_preliminary_rank]);
|
|
bsw@0
|
194 if (params.initiative_max_final_rank) query.addWhere(['initiative.preliminary_rank <= ?', params.initiative_max_final_rank]);
|
|
bsw@0
|
195
|
|
bsw@20
|
196 if (params.initiative_disqualified == '1') query.addWhere('initiative.disqualified');
|
|
bsw@20
|
197 if (params.initiative_disqualified == '0') query.addWhere('NOT initiative.disqualified');
|
|
bsw@20
|
198 if (params.initiative_winner == '1') query.addWhere('initiative.winner');
|
|
bsw@20
|
199 if (params.initiative_winner == '0') query.addWhere('NOT initiative.winner');
|
|
bsw@0
|
200
|
|
bsw@0
|
201 if (params.initiative_search) {
|
|
bsw@0
|
202 query.addWhere(['initiative.text_search_data @@ text_search_query(?)', params.initiative_search]);
|
|
bsw@0
|
203 };
|
|
bsw@0
|
204 if (params.initiative_order_by_id) {
|
|
bsw@0
|
205 query.addOrderBy('initiative.id');
|
|
bsw@0
|
206 }
|
|
bsw@0
|
207 }
|
|
bsw@0
|
208
|
|
bsw@0
|
209 // add suggestion related options to a db query according to parameters given by client
|
|
bsw@0
|
210 exports.addSuggestionOptions = function (req, query, params) {
|
|
bsw@0
|
211 exports.addInitiativeOptions(req, query, params);
|
|
bsw@0
|
212 if (params.suggestion_id) {
|
|
bsw@0
|
213 query.addWhere(['suggestion.id IN (??)', params.suggestion_id]);
|
|
bsw@0
|
214 }
|
|
bsw@0
|
215 if (params.suggestion_search) {
|
|
bsw@0
|
216 query.addWhere(['suggestion.text_search_data @@ text_search_query(?)', params.suggestion_search]);
|
|
bsw@0
|
217 };
|
|
bsw@0
|
218 if (params.include_suggestion) {
|
|
bsw@0
|
219 addObjectFields(query, 'suggestion');
|
|
bsw@0
|
220 };
|
|
bsw@0
|
221 }
|