1 | <?php |
---|
2 | /***************************************************************************\ |
---|
3 | * eGroupWare - Contacts Center * |
---|
4 | * http://www.egroupware.org * |
---|
5 | * Written by: * |
---|
6 | * - Raphael Derosso Pereira <raphaelpereira@users.sourceforge.net> * |
---|
7 | * sponsored by Thyamad - http://www.thyamad.com |
---|
8 | * ------------------------------------------------------------------------- * |
---|
9 | * This program is free software; you can redistribute it and/or modify it * |
---|
10 | * under the terms of the GNU General Public License as published by the * |
---|
11 | * Free Software Foundation; either version 2 of the License, or (at your * |
---|
12 | * option) any later version. * |
---|
13 | \***************************************************************************/ |
---|
14 | |
---|
15 | class abo_catalog |
---|
16 | { |
---|
17 | var $db; |
---|
18 | |
---|
19 | var $security; |
---|
20 | |
---|
21 | /*! |
---|
22 | @attr array Tables |
---|
23 | @abstract The main descriptor between high-level field indication |
---|
24 | and low-level table relations |
---|
25 | @author Raphael Derosso Pereira (conception and code) |
---|
26 | @author Vinicius Cubas Brand (conception) |
---|
27 | */ |
---|
28 | |
---|
29 | var $tables = array( |
---|
30 | 'status' => array( |
---|
31 | 'table' => 'phpgw_cc_status' |
---|
32 | ), |
---|
33 | |
---|
34 | 'prefix' => array( |
---|
35 | 'table' => 'phpgw_cc_prefixes' |
---|
36 | ), |
---|
37 | |
---|
38 | 'suffix' => array( |
---|
39 | 'table' => 'phpgw_cc_suffixes' |
---|
40 | ), |
---|
41 | |
---|
42 | 'contact' => array( |
---|
43 | 'table' => 'phpgw_cc_contact', |
---|
44 | |
---|
45 | 'status' => 'id_status,id_status', |
---|
46 | 'prefix' => 'id_prefix,id_prefix', |
---|
47 | 'suffix' => 'id_suffix,id_suffix', |
---|
48 | |
---|
49 | 'contact_related' => 'id_contact,id_contact', |
---|
50 | 'related' => array('shortcut' => 'contact_related'), |
---|
51 | |
---|
52 | 'contact_connection' => 'id_contact,id_contact', |
---|
53 | 'connection' => array('path' => 'contact_connection'), |
---|
54 | |
---|
55 | 'contact_address' => 'id_contact,id_contact', |
---|
56 | 'address' => array('path' => 'contact_address'), |
---|
57 | |
---|
58 | 'business_info' => 'id_contact,id_contact', |
---|
59 | 'company' => array('path' => 'business_info') |
---|
60 | ), |
---|
61 | |
---|
62 | 'business_info' => array( |
---|
63 | 'table' => 'phpgw_cc_contact_company', |
---|
64 | 'company' => 'id_company,id_company' |
---|
65 | ), |
---|
66 | |
---|
67 | 'group' => array('phpgw_cc_groups', |
---|
68 | 'table' => 'phpgw_cc_groups', |
---|
69 | ), |
---|
70 | |
---|
71 | 'company' => array( |
---|
72 | 'table' => 'phpgw_cc_company', |
---|
73 | |
---|
74 | 'company_related' => 'id_company.id_company', |
---|
75 | 'related' => array('shortcut' => 'company_related'), |
---|
76 | |
---|
77 | 'company_address' => 'id_company,id_company', |
---|
78 | 'address' => array('path' => 'company_address'), |
---|
79 | |
---|
80 | 'company_connection' => 'id_company,id_company', |
---|
81 | 'connection' => array('path' => 'company_connection'), |
---|
82 | |
---|
83 | 'business_info' => 'id_company,id_company', |
---|
84 | 'contact' => array('path' => 'business_info'), |
---|
85 | |
---|
86 | 'legal' => 'id_company,id_company', |
---|
87 | ), |
---|
88 | |
---|
89 | 'company_related' => array( |
---|
90 | 'table' => 'phpgw_cc_company_rels', |
---|
91 | 'company' => 'id_related,id_company', |
---|
92 | |
---|
93 | 'company_related' => 'id_related,id_company', |
---|
94 | 'related' => array('shortcut' => 'company_related'), |
---|
95 | |
---|
96 | 'typeof_company_relation' => 'id_typeof_company_relation,id_typeof_company_relation', |
---|
97 | 'typeof_relation' => array('shortcut' => 'typeof_company_relation'), |
---|
98 | 'type' => array('shortcut' => 'typeof_company_relation') |
---|
99 | ), |
---|
100 | 'contact_related' => array( |
---|
101 | 'table' => 'phpgw_cc_contact_rels', |
---|
102 | 'contact' => 'id_related,id_contact', |
---|
103 | |
---|
104 | 'contact_related' => 'id_contact,id_related', |
---|
105 | 'related' => array('shortcut' => 'contact_related'), |
---|
106 | |
---|
107 | 'typeof_contact_relation' => 'id_typeof_contact_relation,id_typeof_contact_relation', |
---|
108 | 'typeof_relation' => array('shortcut' => 'typeof_contact_relation'), |
---|
109 | 'type' => array('shortcut' => 'typeof_contact_relation') |
---|
110 | ), |
---|
111 | |
---|
112 | 'company_address' => array( |
---|
113 | 'table' => 'phpgw_cc_company_addrs', |
---|
114 | 'address' => 'id_address,id_address', |
---|
115 | 'typeof_address' => array('shortcut' => 'typeof_company_address'), |
---|
116 | 'type' => array('shortcut' => 'typeof_company_address') |
---|
117 | ), |
---|
118 | 'contact_address' => array( |
---|
119 | 'table' => 'phpgw_cc_contact_addrs', |
---|
120 | 'address' => 'id_address,id_address', |
---|
121 | 'typeof_address' => array('shortcut' => 'typeof_contact_address'), |
---|
122 | 'type' => array('shortcut' => 'typeof_contact_address') |
---|
123 | ), |
---|
124 | 'address' => array( |
---|
125 | 'table' => 'phpgw_cc_addresses', |
---|
126 | 'city' => 'id_city,id_city' |
---|
127 | ), |
---|
128 | 'city' => array( |
---|
129 | 'table' => 'phpgw_cc_city', |
---|
130 | 'state' => 'id_state,id_state', |
---|
131 | 'country' => 'id_country,id_country' |
---|
132 | ), |
---|
133 | 'state' => array( |
---|
134 | 'table' => 'phpgw_cc_state', |
---|
135 | 'country' => 'id_country,id_country', |
---|
136 | ), |
---|
137 | 'country' => array( |
---|
138 | 'table' => 'phpgw_cc_country' |
---|
139 | ), |
---|
140 | |
---|
141 | 'company_connection' => array( |
---|
142 | 'table' => 'phpgw_cc_company_conns', |
---|
143 | 'connection' => 'id_connection,id_connection', |
---|
144 | 'typeof_connection' => array('shortcut' => 'typeof_company_connection'), |
---|
145 | 'type' => array('shortcut' => 'typeof_company_connection') |
---|
146 | ), |
---|
147 | 'contact_connection' => array( |
---|
148 | 'table' => 'phpgw_cc_contact_conns', |
---|
149 | 'connection' => 'id_connection,id_connection', |
---|
150 | 'typeof_connection' => array('shortcut' => 'typeof_contact_connection'), |
---|
151 | 'type' => array('shortcut' => 'typeof_contact_connection') |
---|
152 | ), |
---|
153 | 'connection' => array( |
---|
154 | 'table' => 'phpgw_cc_connections' |
---|
155 | ), |
---|
156 | |
---|
157 | 'legal' => array( |
---|
158 | 'table' => 'phpgw_cc_company_legals', |
---|
159 | 'typeof_company_legal' => 'id_typeof_company_legal,id_typeof_company_legal', |
---|
160 | 'typeof_legal' => array('shortcut' => 'typeof_company_legal'), |
---|
161 | 'type' => array('shortcut' => 'typeof_company_legal') |
---|
162 | ), |
---|
163 | |
---|
164 | |
---|
165 | 'typeof_contact_relation' => array( |
---|
166 | 'table' => 'phpgw_cc_typeof_ct_rels' |
---|
167 | ), |
---|
168 | 'typeof_company_relation' => array( |
---|
169 | 'table' => 'phpgw_cc_typeof_co_rels' |
---|
170 | ), |
---|
171 | 'typeof_contact_address' => array( |
---|
172 | 'table' => 'phpgw_cc_typeof_ct_addrs' |
---|
173 | ), |
---|
174 | 'typeof_company_address' => array( |
---|
175 | 'table' => 'phpgw_cc_typeof_co_addrs' |
---|
176 | ), |
---|
177 | 'typeof_contact_connection' => array( |
---|
178 | 'table' => 'phpgw_cc_typeof_ct_conns' |
---|
179 | ), |
---|
180 | 'typeof_company_connection' => array( |
---|
181 | 'table' => 'phpgw_cc_typeof_co_conns' |
---|
182 | ), |
---|
183 | 'typeof_company_legal' => array( |
---|
184 | 'table' => 'phpgw_cc_typeof_co_legals' |
---|
185 | ) |
---|
186 | ); |
---|
187 | |
---|
188 | function init() |
---|
189 | { |
---|
190 | $this->db = $GLOBALS['phpgw']->db; |
---|
191 | $this->security = CreateObject('contactcenter.security_manager'); |
---|
192 | } |
---|
193 | |
---|
194 | /*! |
---|
195 | |
---|
196 | @function sql_find |
---|
197 | @abstract Performs a search in the DB based on the parameters |
---|
198 | @author Raphael Derosso Pereira (algorithm and code) |
---|
199 | @author Vinicius Cubas Brand (algorithm) |
---|
200 | |
---|
201 | @param array $what The list of fields to be returned. The format is: |
---|
202 | $what = array( |
---|
203 | 'contact.company.company_name', |
---|
204 | 'contact.names_ordered' |
---|
205 | ); |
---|
206 | |
---|
207 | @param array $rules The restrictions. |
---|
208 | |
---|
209 | The restrictions format is quite complicated, but is very complete. |
---|
210 | As defined here, there is the possibility to do almost any type of |
---|
211 | search (tell me if you can't do any). For example, imagine the |
---|
212 | following search: |
---|
213 | |
---|
214 | and(a,or(d,e,and(f,g))) |
---|
215 | |
---|
216 | That is represented by the folloowing tree: |
---|
217 | |
---|
218 | and |
---|
219 | | |
---|
220 | .--------------------. |
---|
221 | | | |
---|
222 | a = 5 or |
---|
223 | | |
---|
224 | .---------.------------. |
---|
225 | | | | |
---|
226 | d != 10 e LIKE %a and |
---|
227 | | |
---|
228 | .-------. |
---|
229 | | | |
---|
230 | f = 5 g < 10 |
---|
231 | |
---|
232 | |
---|
233 | The rules that should be passed to the find function for this tree |
---|
234 | is: |
---|
235 | |
---|
236 | $rules = array( |
---|
237 | 0 => array( |
---|
238 | 'field' => 'A', |
---|
239 | 'type' => '=', |
---|
240 | 'value' => 5 |
---|
241 | ), |
---|
242 | 1 => array ( |
---|
243 | 'type' => 'branch', |
---|
244 | 'value' => 'OR', |
---|
245 | 'sub_branch' => array( |
---|
246 | 0 => array( |
---|
247 | 'field' => 'D' |
---|
248 | 'type' => '!=', |
---|
249 | 'value' => 10 |
---|
250 | ), |
---|
251 | 1 => array( |
---|
252 | 'field' => 'E', |
---|
253 | 'type' => 'LIKE', |
---|
254 | 'value' => '%a' |
---|
255 | ) |
---|
256 | 2 => array( |
---|
257 | 'type' => 'branch', |
---|
258 | 'value' => 'AND', |
---|
259 | 'sub_branch' => array( |
---|
260 | 0 => array( |
---|
261 | 'field' => 'F', |
---|
262 | 'type' => '=', |
---|
263 | 'value' => 5 |
---|
264 | ), |
---|
265 | 1 => array( |
---|
266 | 'field' => 'G' |
---|
267 | 'type' => '<', |
---|
268 | 'value' => 10 |
---|
269 | ) |
---|
270 | ) |
---|
271 | ) |
---|
272 | ) |
---|
273 | ) |
---|
274 | ); |
---|
275 | |
---|
276 | |
---|
277 | The restriction type can be: =, !=, <=, <, >, >=, NULL, IN, LIKE, |
---|
278 | NOT NULL, NOT IN, NOT LIKE |
---|
279 | Value of branch can be AND, OR, NOT |
---|
280 | |
---|
281 | @param array $other Other parameter to the search |
---|
282 | $other = array( |
---|
283 | 'offset' => (int), |
---|
284 | 'limit' => (int), |
---|
285 | 'order' => (string with field names separated by commas) |
---|
286 | 'sort' => {ASC|DESC}, |
---|
287 | 'fields_modifier' => (COUNT|MAX) |
---|
288 | ); |
---|
289 | |
---|
290 | @return array $array[<field_name>][<row_number>] |
---|
291 | |
---|
292 | */ |
---|
293 | function sql_find($what, $rules=false, $other=false) |
---|
294 | { |
---|
295 | if (!is_array($what)) |
---|
296 | { |
---|
297 | exit('Error'); |
---|
298 | } |
---|
299 | |
---|
300 | $tables_def = $GLOBALS['phpgw']->db->get_table_definitions('contactcenter'); |
---|
301 | |
---|
302 | $query_select = 'SELECT '; |
---|
303 | $query_from = array(); |
---|
304 | $query_fields_joins = array(); |
---|
305 | $query_restric_joins = array(); |
---|
306 | $query_wheres = array(); |
---|
307 | $tables_as = array(); |
---|
308 | |
---|
309 | $n_fields = count($what); |
---|
310 | for($i = 0; $i < $n_fields; $i++) |
---|
311 | { |
---|
312 | $path = $this->get_tables_by_field($what[$i], $tables_def); |
---|
313 | $n_tables = count($path); |
---|
314 | |
---|
315 | $fields[] = $path[$n_tables-1]; |
---|
316 | $fields_translate[$what[$i]] = $path[$n_tables-1]; |
---|
317 | |
---|
318 | list($from_t,) = explode('.',$path[0]); |
---|
319 | $query_from[] = $from_t; |
---|
320 | |
---|
321 | $last_non_unique = false; |
---|
322 | for($j = 0; $j < $n_tables-1; $j += 2) |
---|
323 | { |
---|
324 | list($left_t,$left_k) = explode('.',$path[$j+1]); |
---|
325 | list($right_t,$right_k) = explode('.',$path[$j]); |
---|
326 | |
---|
327 | $query_join_t = 'LEFT JOIN '.$left_t.' AS t0'.$i.$j.' ON t0'. |
---|
328 | $i.$j.'.'.$left_k.'='; |
---|
329 | |
---|
330 | $query_join_reg = 'LEFT JOIN '.$left_t.' AS t[0-9]{2,} ON t[0-9]{2,}'. |
---|
331 | '\.'.$left_k.'='; |
---|
332 | |
---|
333 | $t = $j-2; |
---|
334 | if ($j == 0) |
---|
335 | { |
---|
336 | $query_join_t .= $path[$j]; |
---|
337 | $query_join_reg .= $path[$j]; |
---|
338 | } |
---|
339 | else |
---|
340 | { |
---|
341 | $query_join_t .= 't0'.$i.$t.'.'.$right_k; |
---|
342 | $query_join_reg .= 't[0-9]{2,}\.'.$right_k; |
---|
343 | } |
---|
344 | |
---|
345 | $query_fields_joins[] = $query_join_t; |
---|
346 | $tables_as[] = 't0'.$i.$j; |
---|
347 | continue; |
---|
348 | |
---|
349 | /* TODO: The code below detects the double JOIN lines, but |
---|
350 | * the problem is that it doesn't do that correctly, because |
---|
351 | * it should consider the hole path, not just one entry. |
---|
352 | */ |
---|
353 | $unique = true; |
---|
354 | if ($n_joins = count($query_fields_joins)) |
---|
355 | { |
---|
356 | for($k = 0; $k < $n_joins; $k++) |
---|
357 | { |
---|
358 | if (preg_match("/$query_join_reg/",$query_fields_joins[$k])) |
---|
359 | { |
---|
360 | $unique = false; |
---|
361 | $last_non_unique = $k; |
---|
362 | } |
---|
363 | } |
---|
364 | } |
---|
365 | |
---|
366 | if ($unique and $j != 0) |
---|
367 | { |
---|
368 | if ($j != 0) |
---|
369 | { |
---|
370 | if ($last_non_unique === false) |
---|
371 | { |
---|
372 | $query_join_t .= 't0'.$i.$t.'.'.$right_k; |
---|
373 | } |
---|
374 | else |
---|
375 | { |
---|
376 | $query_join_t .= $tables_as[$last_non_unique].'.'.$right_k; |
---|
377 | } |
---|
378 | } |
---|
379 | $query_fields_joins[] = $query_join_t; |
---|
380 | $tables_as[] = 't0'.$i.$j; |
---|
381 | |
---|
382 | $last_non_unique = false; |
---|
383 | } |
---|
384 | } |
---|
385 | } |
---|
386 | |
---|
387 | $restric_fields = $this->get_fields_from_restrictions($rules); |
---|
388 | $n_restrictions = count($restric_fields); |
---|
389 | |
---|
390 | for ($i = 0; $i < $n_restrictions; $i++) |
---|
391 | { |
---|
392 | $path = $this->get_tables_by_field($restric_fields[$i], $tables_def); |
---|
393 | $n_tables = count($path); |
---|
394 | |
---|
395 | $last_non_unique = false; |
---|
396 | for($j = 0; $j < $n_tables-1; $j += 2) |
---|
397 | { |
---|
398 | list($left_t,$left_k) = explode('.',$path[$j+1]); |
---|
399 | list($right_t,$right_k) = explode('.',$path[$j]); |
---|
400 | |
---|
401 | $query_join_t = 'LEFT JOIN '.$left_t.' AS t1'.$i.$j.' ON t1'. |
---|
402 | $i.$j.'.'.$left_k.'='; |
---|
403 | |
---|
404 | $query_join_reg = 'LEFT JOIN '.$left_t.' AS t[0-9]{2,} ON t[0-9]{2,}'. |
---|
405 | '\.'.$left_k.'='; |
---|
406 | |
---|
407 | $t = $j-2; |
---|
408 | if ($j == 0) |
---|
409 | { |
---|
410 | $query_join_t .= $path[$j]; |
---|
411 | $query_join_reg .= $path[$j]; |
---|
412 | } |
---|
413 | else |
---|
414 | { |
---|
415 | $query_join_t .= 't1'.$i.$t.'.'.$right_k; |
---|
416 | $query_join_reg .= 't[0-9]{2,}\.'.$right_k; |
---|
417 | } |
---|
418 | |
---|
419 | $query_restric_joins[] = $query_join_t; |
---|
420 | $tables_as[] = 't1'.$i.$j; |
---|
421 | continue; |
---|
422 | |
---|
423 | /* TODO: The code below detects the double JOIN lines, but |
---|
424 | * the problem is that it doesn't do that correctly, because |
---|
425 | * it should consider the hole path, not just one entry. |
---|
426 | */ |
---|
427 | $unique = true; |
---|
428 | if ($n_joins = count($query_restric_joins)) |
---|
429 | { |
---|
430 | for($k = 0; $k < $n_joins; $k++) |
---|
431 | { |
---|
432 | if (preg_match("/$query_join_reg/",$query_restric_joins[$k])) |
---|
433 | { |
---|
434 | $unique = false; |
---|
435 | $last_non_unique = $k; |
---|
436 | } |
---|
437 | } |
---|
438 | } |
---|
439 | |
---|
440 | if ($unique) |
---|
441 | { |
---|
442 | if ($j != 0) |
---|
443 | { |
---|
444 | if ($last_non_unique === false) |
---|
445 | { |
---|
446 | $query_join_t .= 't1'.$i.$t.'.'.$right_k; |
---|
447 | } |
---|
448 | else |
---|
449 | { |
---|
450 | $query_join_t .= $tables_as[$last_non_unique].'.'.$right_k; |
---|
451 | } |
---|
452 | } |
---|
453 | $query_restric_joins[] = $query_join_t; |
---|
454 | $tables_as[] = 't1'.$i.$j; |
---|
455 | |
---|
456 | $last_non_unique = false; |
---|
457 | } |
---|
458 | } |
---|
459 | |
---|
460 | list($table,$field_r) = explode('.',$path[$n_tables-1]); |
---|
461 | |
---|
462 | if ($n_tables > 1) |
---|
463 | { |
---|
464 | $t = $j-2; |
---|
465 | $tables_restric[$restric_fields[$i]] = array( |
---|
466 | 'table' => 't1'.$i.$t, |
---|
467 | 'field' => $field_r |
---|
468 | ); |
---|
469 | } |
---|
470 | else |
---|
471 | { |
---|
472 | $tables_restric[$restric_fields[$i]] = array( |
---|
473 | 'table' => $table, |
---|
474 | 'field' => $field_r |
---|
475 | ); |
---|
476 | } |
---|
477 | |
---|
478 | } |
---|
479 | |
---|
480 | if ($other) |
---|
481 | { |
---|
482 | foreach($other as $name => $value) |
---|
483 | { |
---|
484 | switch($name) |
---|
485 | { |
---|
486 | case 'offset': |
---|
487 | if(is_int($value)) |
---|
488 | { |
---|
489 | $query_other[2] = 'OFFSET '.$value; |
---|
490 | } |
---|
491 | break; |
---|
492 | |
---|
493 | case 'limit': |
---|
494 | if(is_int($value)) |
---|
495 | { |
---|
496 | $query_other[3] = 'LIMIT '.$value; |
---|
497 | } |
---|
498 | break; |
---|
499 | |
---|
500 | case 'order': |
---|
501 | $order_fs = explode(',',$value); |
---|
502 | foreach($order_fs as $order_f) |
---|
503 | { |
---|
504 | $query_other[0] = 'ORDER BY '.$fields_translate[$order_f]; |
---|
505 | } |
---|
506 | break; |
---|
507 | |
---|
508 | case 'sort': |
---|
509 | switch($value) |
---|
510 | { |
---|
511 | case 'ASC': |
---|
512 | case 'DESC': |
---|
513 | $query_other[1] = $value; |
---|
514 | } |
---|
515 | break; |
---|
516 | |
---|
517 | case 'fields_modifier': |
---|
518 | switch($value) |
---|
519 | { |
---|
520 | case 'COUNT': |
---|
521 | case 'MAX': |
---|
522 | $query_fields_mod = $value; |
---|
523 | } |
---|
524 | break; |
---|
525 | case 'customFilter': |
---|
526 | case 'exact': |
---|
527 | case 'CN': |
---|
528 | break; |
---|
529 | |
---|
530 | default: |
---|
531 | exit('Invalid \'other\' field passed to find in file '.__FILE__.' on line '.__LINE__); |
---|
532 | } |
---|
533 | } |
---|
534 | } |
---|
535 | |
---|
536 | $query_from = array_unique($query_from); |
---|
537 | if ($query_fields_mod) |
---|
538 | { |
---|
539 | $query_select .= ' '.$query_fields_mod.'('.implode(',',$fields).') AS mod'; |
---|
540 | } |
---|
541 | else |
---|
542 | { |
---|
543 | $query_select .= implode(',',$fields); |
---|
544 | } |
---|
545 | |
---|
546 | @ksort($query_other); |
---|
547 | |
---|
548 | $query_from_f = ' FROM '.implode(',',$query_from); |
---|
549 | |
---|
550 | $query = $query_select . $query_from_f; |
---|
551 | |
---|
552 | if (count($query_fields_joins)) |
---|
553 | { |
---|
554 | $query = ' '.implode("\n",$query_fields_joins); |
---|
555 | } |
---|
556 | |
---|
557 | if (count($query_restric_joins)) |
---|
558 | { |
---|
559 | $query .= ' '.implode("\n",$query_restric_joins); |
---|
560 | } |
---|
561 | |
---|
562 | if ($rules) |
---|
563 | { |
---|
564 | $query .= ' WHERE '.$this->process_restrictions($rules,$tables_restric); |
---|
565 | } |
---|
566 | |
---|
567 | if (count($query_other)) |
---|
568 | { |
---|
569 | if (!($query_other[1] and !$query_other[0])) |
---|
570 | { |
---|
571 | $query .= ' '.@implode(' ',$query_other); |
---|
572 | } |
---|
573 | } |
---|
574 | |
---|
575 | //echo 'Query in Find: "'.$query.'"<br>'; |
---|
576 | if (!$this->db->query($query)) |
---|
577 | { |
---|
578 | exit ('Query failed! File: '.__FILE__.' on line'.__LINE__); |
---|
579 | } |
---|
580 | |
---|
581 | $return = false; |
---|
582 | while($this->db->next_record()) |
---|
583 | { |
---|
584 | $return[] = $this->db->row(); |
---|
585 | } |
---|
586 | |
---|
587 | return $return; |
---|
588 | } |
---|
589 | |
---|
590 | /*! |
---|
591 | |
---|
592 | @function get_tables_by_field |
---|
593 | @abstract Returns the table wich holds the specified field |
---|
594 | @author Raphael Derosso Pereira |
---|
595 | |
---|
596 | @param string $field The field to be found |
---|
597 | @param array $tables The array returned by get_db_tables |
---|
598 | */ |
---|
599 | function get_tables_by_field ($field, &$apptables) |
---|
600 | { |
---|
601 | |
---|
602 | $field_parts = explode('.',$field); |
---|
603 | $n_fields = count($field_parts); |
---|
604 | |
---|
605 | $return = array(); |
---|
606 | $previous = false; |
---|
607 | for($i = 0; $i < $n_fields-1; $i++) |
---|
608 | { |
---|
609 | $actual = $field_parts[$i]; |
---|
610 | $next = $field_parts[$i+1]; |
---|
611 | |
---|
612 | if (!isset($apptables[$this->tables[$actual]['table']])) |
---|
613 | { |
---|
614 | exit('Some unrecognized parameter in '.__FILE__.' on line '.__LINE__.'<br>'. |
---|
615 | 'Couldn\'t find '.$this->tables[$actual]['table']); |
---|
616 | } |
---|
617 | |
---|
618 | if (array_key_exists($next,$apptables[$this->tables[$actual]['table']]['fd'])) |
---|
619 | { |
---|
620 | array_push($return, $this->tables[$actual]['table'].'.'.$next); |
---|
621 | continue; |
---|
622 | } |
---|
623 | |
---|
624 | if (array_key_exists($next,$this->tables[$actual])) |
---|
625 | { |
---|
626 | if (is_array($this->tables[$actual][$next])) |
---|
627 | { |
---|
628 | if (isset($this->tables[$actual][$next]['shortcut'])) |
---|
629 | { |
---|
630 | $next = $this->tables[$actual][$next]['shortcut']; |
---|
631 | $field_parts[$i+1] = $next; |
---|
632 | } |
---|
633 | else if (isset($this->tables[$actual][$next]['path'])) |
---|
634 | { |
---|
635 | $path = $this->tables[$actual][$next]['path']; |
---|
636 | $field_parts[$i+1] = $path; |
---|
637 | $field_parts = array_merge(array_slice($field_parts,0,$i+2),$next,array_slice($field_parts,$i+2,count($field_parts))); |
---|
638 | $next = $path; |
---|
639 | $n_fields = count($field_parts); |
---|
640 | } |
---|
641 | } |
---|
642 | |
---|
643 | list($key1, $key2) = explode(',',$this->tables[$actual][$next]); |
---|
644 | array_push($return, $this->tables[$actual]['table'].'.'.$key1, $this->tables[$next]['table'].'.'.$key2); |
---|
645 | continue; |
---|
646 | } |
---|
647 | |
---|
648 | exit('Invalid field in '.__FILE__.' on line '.__LINE__.'<br>Actual: '.$actual.'<br>Next: '.$next); |
---|
649 | } |
---|
650 | |
---|
651 | return $return; |
---|
652 | } |
---|
653 | |
---|
654 | /*! |
---|
655 | |
---|
656 | @function get_fields_from_restrictions |
---|
657 | @abstract Returns an array containing the fields inside the restrictions |
---|
658 | ignoring the branches |
---|
659 | @author Raphael Derosso Pereira |
---|
660 | |
---|
661 | @param array $restrictions The restrictions |
---|
662 | |
---|
663 | */ |
---|
664 | function get_fields_from_restrictions(&$restrictions) |
---|
665 | { |
---|
666 | if (!is_array($restrictions)) |
---|
667 | { |
---|
668 | return null; |
---|
669 | } |
---|
670 | |
---|
671 | $fields = array(); |
---|
672 | |
---|
673 | foreach ($restrictions as $restrict_data) |
---|
674 | { |
---|
675 | switch($restrict_data['type']) |
---|
676 | { |
---|
677 | case 'branch': |
---|
678 | $fields = array_merge($fields, $this->get_fields_from_restrictions($restrict_data['sub_branch'])); |
---|
679 | break; |
---|
680 | |
---|
681 | case '=': |
---|
682 | case '!=': |
---|
683 | case '<=': |
---|
684 | case '<': |
---|
685 | case '>': |
---|
686 | case '>=': |
---|
687 | case 'NULL': |
---|
688 | case 'IN': |
---|
689 | case 'LIKE': |
---|
690 | case 'iLIKE': |
---|
691 | case 'LIKE and ~=': |
---|
692 | case 'NOT NULL': |
---|
693 | case 'NOT IN': |
---|
694 | case 'NOT LIKE': |
---|
695 | case 'NOT iLIKE': |
---|
696 | array_push($fields, $restrict_data['field']); |
---|
697 | break; |
---|
698 | |
---|
699 | default: |
---|
700 | exit('Error in '.__FILE__.' on '.__LINE__.'<br>The restriction type passed was: '.$restrict_data['type']); |
---|
701 | } |
---|
702 | } |
---|
703 | |
---|
704 | return $fields; |
---|
705 | } |
---|
706 | |
---|
707 | /*! |
---|
708 | |
---|
709 | @function process_restrictions |
---|
710 | @abstract Takes the restrictions array and returns an string |
---|
711 | that corresponds to the array |
---|
712 | @author Raphael Derosso Pereira |
---|
713 | |
---|
714 | @param array $restriction The restriction array |
---|
715 | @param array $associative_tree The |
---|
716 | @param string $logic_type The type of the logic that should be |
---|
717 | used to join the fields |
---|
718 | |
---|
719 | */ |
---|
720 | function process_restrictions(&$restrictions, &$associative_tree, $join_type='AND' ) |
---|
721 | { |
---|
722 | foreach($restrictions as $restrict_index => $restrict_data) |
---|
723 | { |
---|
724 | switch($restrict_data['type']) |
---|
725 | { |
---|
726 | case 'branch': |
---|
727 | $return_t[] = ' ('.$this->process_restrictions($restrict_data['sub_branch'],$associative_tree,$restrict_data['value']).') '; |
---|
728 | break; |
---|
729 | |
---|
730 | case 'iLIKE': |
---|
731 | $return_t[] = 'UPPER( translate('.$associative_tree[$restrict_data['field']]['table'].'.'. |
---|
732 | $associative_tree[$restrict_data['field']]['field'].',\'áàâãäéèêëíìïóòôõöúùûüÁÀÂÃÄÉÈÊËÍÌÏÓÒÔÕÖÚÙÛÜçÇñÑ\',\'aaaaaeeeeiiiooooouuuuAAAAAEEEEIIIOOOOOUUUUcCnN\')) LIKE UPPER(translate(\''.$restrict_data['value'].'\', \'áàâãäéèêëíìïóòôõöúùûüÁÀÂÃÄÉÈÊËÍÌÏÓÒÔÕÖÚÙÛÜçÇñÑ\',\'aaaaaeeeeiiiooooouuuuAAAAAEEEEIIIOOOOOUUUUcCnN\'))'; |
---|
733 | break; |
---|
734 | |
---|
735 | case 'LIKE and ~=': |
---|
736 | $return_t[] = 'UPPER(translate('.$associative_tree[$restrict_data['field']]['table'].'.'. |
---|
737 | $associative_tree[$restrict_data['field']]['field'].',\'áàâãäéèêëíìïóòôõöúùûüÁÀÂÃÄÉÈÊËÍÌÏÓÒÔÕÖÚÙÛÜçÇñÑ\',\'aaaaaeeeeiiiooooouuuuAAAAAEEEEIIIOOOOOUUUUcCnN\')) LIKE UPPER(translate(\''."%".($restrict_data['value'])."%".'\', \'áàâãäéèêëíìïóòôõöúùûüÁÀÂÃÄÉÈÊËÍÌÏÓÒÔÕÖÚÙÛÜçÇñÑ\',\'aaaaaeeeeiiiooooouuuuAAAAAEEEEIIIOOOOOUUUUcCnN\'))'; |
---|
738 | break; |
---|
739 | |
---|
740 | case 'NOT iLIKE': |
---|
741 | $return_t[] = 'UPPER( '.$associative_tree[$restrict_data['field']]['table'].'.'. |
---|
742 | $associative_tree[$restrict_data['field']]['field'].') NOT LIKE UPPER(\''.$restrict_data['value'].'\')'; |
---|
743 | break; |
---|
744 | |
---|
745 | case '=': |
---|
746 | case '!=': |
---|
747 | case '<=': |
---|
748 | case '<': |
---|
749 | case '>': |
---|
750 | case '>=': |
---|
751 | case 'NULL': |
---|
752 | case 'LIKE': |
---|
753 | case 'NOT NULL': |
---|
754 | case 'NOT LIKE': |
---|
755 | $return_t[] = $associative_tree[$restrict_data['field']]['table'].'.'. |
---|
756 | $associative_tree[$restrict_data['field']]['field'].' '.$restrict_data['type'].' \''.$restrict_data['value'].'\''; |
---|
757 | break; |
---|
758 | |
---|
759 | case 'IN': |
---|
760 | case 'NOT IN': |
---|
761 | $return_t[] = $associative_tree[$restrict_data['field']]['table'].'.'. |
---|
762 | $associative_tree[$restrict_data['field']]['field'].' '.$restrict_data['type'].' '.$restrict_data['value']; |
---|
763 | break; |
---|
764 | } |
---|
765 | |
---|
766 | } |
---|
767 | |
---|
768 | if (count($return_t) > 1) |
---|
769 | { |
---|
770 | return(implode(' '.$join_type.' ',$return_t)); |
---|
771 | } |
---|
772 | |
---|
773 | return $return_t[0]; |
---|
774 | } |
---|
775 | |
---|
776 | |
---|
777 | /*********************************************************************\ |
---|
778 | * Data Management * |
---|
779 | \*********************************************************************/ |
---|
780 | |
---|
781 | /*! |
---|
782 | @function get_fields |
---|
783 | @abstract Returns all the fields that a catalog can have |
---|
784 | on an array |
---|
785 | @author Raphael Derosso Pereira |
---|
786 | |
---|
787 | @param bool $all Return filled with True or False? |
---|
788 | |
---|
789 | */ |
---|
790 | function get_fields($all=false) |
---|
791 | { |
---|
792 | if (!is_bool($all)) |
---|
793 | { |
---|
794 | if (is_object($GLOBALS['phpgw']->log)) |
---|
795 | { |
---|
796 | $GLOBALS['phpgw']->log->message(array( |
---|
797 | 'text' => 'F-BadcontactcenterParam, get_contact_fields parameter must be boolean.', |
---|
798 | 'line' => __LINE__, |
---|
799 | 'file' => __FILE__)); |
---|
800 | |
---|
801 | $GLOBALS['phpgw']->log->commit(); |
---|
802 | } |
---|
803 | else { |
---|
804 | exit('Argument Error on: <br>File:'.__FILE__.'<br>Line:'.__LINE__.'<br>'); |
---|
805 | } |
---|
806 | } |
---|
807 | |
---|
808 | if ($all) |
---|
809 | { |
---|
810 | return $this->fields; |
---|
811 | } |
---|
812 | else |
---|
813 | { |
---|
814 | $fields_temp = $this->fields; |
---|
815 | |
---|
816 | foreach(array_keys($fields_temp) as $field) |
---|
817 | { |
---|
818 | $fields_temp[$field] = false; |
---|
819 | } |
---|
820 | |
---|
821 | return $fields_temp; |
---|
822 | } |
---|
823 | } |
---|
824 | |
---|
825 | |
---|
826 | /*********************************************************************\ |
---|
827 | * Methods to Access Shared Catalog Data * |
---|
828 | \*********************************************************************/ |
---|
829 | |
---|
830 | |
---|
831 | /*! |
---|
832 | |
---|
833 | @function get_all_countries |
---|
834 | @abstract Returns all the countries the API provides |
---|
835 | @author Raphael Derosso Pereira |
---|
836 | |
---|
837 | */ |
---|
838 | function get_all_countries() |
---|
839 | { |
---|
840 | $obj = CreateObject('phpgwapi.country'); |
---|
841 | $countries = $obj->country_array; |
---|
842 | unset($countries[' ']); |
---|
843 | |
---|
844 | foreach($countries as $code => $name) |
---|
845 | { |
---|
846 | $name = lang($name); |
---|
847 | $countries[$code] = $name{0} . strtolower(substr($name, 1)); |
---|
848 | } |
---|
849 | |
---|
850 | return $countries; |
---|
851 | } |
---|
852 | |
---|
853 | /*! |
---|
854 | |
---|
855 | @function get_all_states |
---|
856 | @abstract Returns all the states for the given country |
---|
857 | @author Raphael Derosso Pereira |
---|
858 | |
---|
859 | @param $id_country The ID of the Country that contains the requested States |
---|
860 | |
---|
861 | */ |
---|
862 | function get_all_states($id_country) |
---|
863 | { |
---|
864 | $id_states = $this->find(array('state.id_state', 'state.state_name'), |
---|
865 | array( |
---|
866 | 0 => array( |
---|
867 | 'field' => 'state.id_country', |
---|
868 | 'type' => '=', |
---|
869 | 'value' => $id_country |
---|
870 | ) |
---|
871 | ), |
---|
872 | array( |
---|
873 | 'order' => 'state.state_name', |
---|
874 | 'sort' => 'ASC' |
---|
875 | )); |
---|
876 | |
---|
877 | if (!is_array($id_states) || count($id_states) == 0) |
---|
878 | { |
---|
879 | return false; |
---|
880 | } |
---|
881 | |
---|
882 | $result = false; |
---|
883 | foreach($id_states as $id_state) |
---|
884 | { |
---|
885 | $state = CreateObject('contactcenter.so_state', $id_state['id_state']); |
---|
886 | |
---|
887 | $result[$id_state['id_state']]['id_state'] = $state->get_id(); |
---|
888 | $result[$id_state['id_state']]['id_country'] = $state->get_id_country(); |
---|
889 | $result[$id_state['id_state']]['name'] = $state->get_state_name(); |
---|
890 | $result[$id_state['id_state']]['symbol'] = $state->get_state_symbol(); |
---|
891 | } |
---|
892 | |
---|
893 | return $result; |
---|
894 | } |
---|
895 | |
---|
896 | /*! |
---|
897 | |
---|
898 | @function get_all_cities |
---|
899 | @abstract Returns all the cities for the given state |
---|
900 | @author Raphael Derosso Pereira |
---|
901 | |
---|
902 | #param $id_country The ID of the Country that contains the requested Cities |
---|
903 | @param $id_state The ID of the State that contains the requested Cities |
---|
904 | |
---|
905 | */ |
---|
906 | function get_all_cities($id_country, $id_state=false) |
---|
907 | { |
---|
908 | if ($id_state) |
---|
909 | { |
---|
910 | $id_cities = $this->find(array('city.id_city', 'city.city_name'), |
---|
911 | array( |
---|
912 | 0 => array( |
---|
913 | 'field' => 'city.id_state', |
---|
914 | 'type' => '=', |
---|
915 | 'value' => $id_state |
---|
916 | ) |
---|
917 | ), |
---|
918 | array( |
---|
919 | 'order' => 'city.city_name', |
---|
920 | 'sort' => 'ASC' |
---|
921 | )); |
---|
922 | } |
---|
923 | else |
---|
924 | { |
---|
925 | $id_cities = $this->find(array('city.id_city', 'city.city_name'), |
---|
926 | array( |
---|
927 | 0 => array( |
---|
928 | 'field' => 'city.id_country', |
---|
929 | 'type' => '=', |
---|
930 | 'value' => $id_country |
---|
931 | ) |
---|
932 | ), |
---|
933 | array( |
---|
934 | 'order' => 'city.city_name', |
---|
935 | 'sort' => 'ASC' |
---|
936 | )); |
---|
937 | } |
---|
938 | |
---|
939 | if (!is_array($id_cities) || count($id_cities) == 0) |
---|
940 | { |
---|
941 | return false; |
---|
942 | } |
---|
943 | |
---|
944 | $result = false; |
---|
945 | foreach($id_cities as $id_city) |
---|
946 | { |
---|
947 | $city = CreateObject('contactcenter.so_city', $id_city['id_city']); |
---|
948 | |
---|
949 | $result[$id_city['id_city']]['id_city'] = $city->get_id(); |
---|
950 | $result[$id_city['id_city']]['id_country'] = $city->get_id_country(); |
---|
951 | $result[$id_city['id_city']]['id_state'] = $city->get_id_state(); |
---|
952 | $result[$id_city['id_city']]['name'] = $city->get_city_name(); |
---|
953 | $result[$id_city['id_city']]['timezone'] = $city->get_city_timezone(); |
---|
954 | $result[$id_city['id_city']]['geo_location'] = $city->get_city_geo_location(); |
---|
955 | } |
---|
956 | |
---|
957 | return $result; |
---|
958 | } |
---|
959 | |
---|
960 | |
---|
961 | /*********************************************************************\ |
---|
962 | * Methods to Insert/Update Shared Catalog Data * |
---|
963 | \*********************************************************************/ |
---|
964 | |
---|
965 | /*! |
---|
966 | @function add_city |
---|
967 | @abstract Inserts a new City in the DB |
---|
968 | @author Raphael Derosso Pereira |
---|
969 | |
---|
970 | @params array $city_info The city information: |
---|
971 | $city_info = array( |
---|
972 | 'id_state' => (int), |
---|
973 | 'id_country' => (int), MANDATORY |
---|
974 | 'city_name' => (str), MANDATORY |
---|
975 | 'city_time_zone' => (int), |
---|
976 | 'city_geo_location' => (str), |
---|
977 | ); |
---|
978 | |
---|
979 | @return int City ID |
---|
980 | |
---|
981 | */ |
---|
982 | function add_city($city_info) |
---|
983 | { |
---|
984 | if(!is_array($city_info) || !count($city_info) || !$city_info['city_name'] || !$city_info['id_country']) |
---|
985 | { |
---|
986 | exit(print_r(array( |
---|
987 | 'file' => __FILE__, |
---|
988 | 'line' => __LINE__, |
---|
989 | 'msg' => lang('Wrong parameters'), |
---|
990 | 'status' => 'fatal', |
---|
991 | ))); |
---|
992 | } |
---|
993 | |
---|
994 | $permissions = $this->security->get_permissions(); |
---|
995 | |
---|
996 | if (!is_array($permissions['cities']) || array_search('c', $permissions['cities']) === false) |
---|
997 | { |
---|
998 | exit(print_r(array( |
---|
999 | 'file' => __FILE__, |
---|
1000 | 'line' => __LINE__, |
---|
1001 | 'msg' => lang('You does not have sufficient privileges. Aborted!'), |
---|
1002 | 'status' => 'aborted' |
---|
1003 | ))); |
---|
1004 | } |
---|
1005 | |
---|
1006 | /* Search for cities with the same data */ |
---|
1007 | $what = array('city.id_city'); |
---|
1008 | $rules = array( |
---|
1009 | 0 => array( |
---|
1010 | 'field' => 'city.city_name', |
---|
1011 | 'type' => 'iLIKE', |
---|
1012 | 'value' => $city_info['city_name'] |
---|
1013 | ), |
---|
1014 | 1 => array( |
---|
1015 | 'field' => 'city.id_country', |
---|
1016 | 'type' => 'iLIKE', |
---|
1017 | 'value' => $city_info['id_country'] |
---|
1018 | ) |
---|
1019 | ); |
---|
1020 | |
---|
1021 | if (isset($city_info['id_state'])) |
---|
1022 | { |
---|
1023 | array_push($rules, array( |
---|
1024 | 'field' => 'city.id_state', |
---|
1025 | 'type' => '=', |
---|
1026 | 'value' => $city_info['id_state'] |
---|
1027 | )); |
---|
1028 | } |
---|
1029 | |
---|
1030 | $result = $this->find($what, $rules); |
---|
1031 | |
---|
1032 | if (is_array($result) and count($result)) |
---|
1033 | { |
---|
1034 | return $result[0]['id_city']; |
---|
1035 | } |
---|
1036 | |
---|
1037 | $city = CreateObject('contactcenter.so_city'); |
---|
1038 | |
---|
1039 | $city->set_id_country($city_info['id_country']); |
---|
1040 | $city->set_city_name($city_info['city_name']); |
---|
1041 | isset($city_info['id_state']) ? $city->set_id_state($city_info['id_state']) : null; |
---|
1042 | isset($city_info['city_timezone']) ? $city->set_city_timezone($city_info['city_timezone']) : null; |
---|
1043 | isset($city_info['city_geo_location']) ? $city->set_city_geo_location($city_info['city_geo_location']) : null; |
---|
1044 | |
---|
1045 | $city->commit(); |
---|
1046 | $id = $city->get_id(); |
---|
1047 | |
---|
1048 | return $id; |
---|
1049 | } |
---|
1050 | |
---|
1051 | |
---|
1052 | /*! |
---|
1053 | @function add_state |
---|
1054 | @abstract Inserts a new State in the DB |
---|
1055 | @author Raphael Derosso Pereira |
---|
1056 | |
---|
1057 | @params array $state_info The state information: |
---|
1058 | $state_info = array( |
---|
1059 | 'id_country' => (int), MANDATORY |
---|
1060 | 'state_name' => (str), MANDATORY |
---|
1061 | 'state_symbol' => (str), |
---|
1062 | ); |
---|
1063 | |
---|
1064 | @return int State ID |
---|
1065 | |
---|
1066 | */ |
---|
1067 | function add_state($state_info) |
---|
1068 | { |
---|
1069 | if(!is_array($state_info) || !count($state_info) || !$state_info['state_name'] || !$state_info['id_country']) |
---|
1070 | { |
---|
1071 | exit(print_r(array( |
---|
1072 | 'msg' => lang('Wrong parameters'), |
---|
1073 | 'status' => 'fatal' |
---|
1074 | ))); |
---|
1075 | } |
---|
1076 | |
---|
1077 | $permissions = $this->security->get_permissions(); |
---|
1078 | |
---|
1079 | if (!is_array($permissions['states']) || array_search('c', $permissions['states']) === false) |
---|
1080 | { |
---|
1081 | exit(print_r(array( |
---|
1082 | 'msg' => lang('You does not have sufficient privileges. Aborted!'), |
---|
1083 | 'status' => 'aborted' |
---|
1084 | ))); |
---|
1085 | } |
---|
1086 | |
---|
1087 | /* Search for states with the same data */ |
---|
1088 | $what = array('state.id_state'); |
---|
1089 | $rules = array( |
---|
1090 | 0 => array( |
---|
1091 | 'field' => 'state.state_name', |
---|
1092 | 'type' => 'iLIKE', |
---|
1093 | 'value' => $state_info['state_name'] |
---|
1094 | ), |
---|
1095 | 1 => array( |
---|
1096 | 'field' => 'state.id_country', |
---|
1097 | 'type' => 'iLIKE', |
---|
1098 | 'value' => $state_info['id_country'] |
---|
1099 | ) |
---|
1100 | ); |
---|
1101 | |
---|
1102 | $result = $this->find($what, $rules); |
---|
1103 | |
---|
1104 | if (is_array($result) and count($result)) |
---|
1105 | { |
---|
1106 | return $result[0]['id_state']; |
---|
1107 | } |
---|
1108 | |
---|
1109 | $state = CreateObject('contactcenter.so_state'); |
---|
1110 | |
---|
1111 | $state->set_id_country($state_info['id_country']); |
---|
1112 | $state->set_state_name($state_info['state_name']); |
---|
1113 | isset($state_info['state_symbol']) ? $state->set_state_symbol($state_info['state_symbol']) : null; |
---|
1114 | |
---|
1115 | $state->commit(); |
---|
1116 | $id = $state->get_id(); |
---|
1117 | |
---|
1118 | return $id; |
---|
1119 | } |
---|
1120 | |
---|
1121 | } |
---|
1122 | ?> |
---|