4 if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');
5 /*********************************************************************************
6 * SugarCRM is a customer relationship management program developed by
7 * SugarCRM, Inc. Copyright (C) 2004-2011 SugarCRM Inc.
9 * This program is free software; you can redistribute it and/or modify it under
10 * the terms of the GNU Affero General Public License version 3 as published by the
11 * Free Software Foundation with the addition of the following permission added
12 * to Section 15 as permitted in Section 7(a): FOR ANY PART OF THE COVERED WORK
13 * IN WHICH THE COPYRIGHT IS OWNED BY SUGARCRM, SUGARCRM DISCLAIMS THE WARRANTY
14 * OF NON INFRINGEMENT OF THIRD PARTY RIGHTS.
16 * This program is distributed in the hope that it will be useful, but WITHOUT
17 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
18 * FOR A PARTICULAR PURPOSE. See the GNU Affero General Public License for more
21 * You should have received a copy of the GNU Affero General Public License along with
22 * this program; if not, see http://www.gnu.org/licenses or write to the Free
23 * Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
26 * You can contact SugarCRM, Inc. headquarters at 10050 North Wolfe Road,
27 * SW2-130, Cupertino, CA 95014, USA. or at email address contact@sugarcrm.com.
29 * The interactive user interfaces in modified source and object code versions
30 * of this program must display Appropriate Legal Notices, as required under
31 * Section 5 of the GNU Affero General Public License version 3.
33 * In accordance with Section 7(b) of the GNU Affero General Public License version 3,
34 * these Appropriate Legal Notices must retain the display of the "Powered by
35 * SugarCRM" logo. If the display of the logo is not reasonably feasible for
36 * technical reasons, the Appropriate Legal Notices must display the words
37 * "Powered by SugarCRM".
38 ********************************************************************************/
40 require_once('soap/SoapError.php');
42 function check_for_relationship($relationships, $module){
43 foreach($relationships as $table=>$rel){
44 if( $rel['rhs_key'] == $module){
53 * takes in two modules and returns the relationship information about them
57 function retrieve_relationships_properties($module_1, $module_2, $relationship_name = ""){
59 $rs = new Relationship();
60 $query = "SELECT * FROM $rs->table_name WHERE ((lhs_module = '$module_1' AND rhs_module='$module_2') OR (lhs_module = '$module_2' AND rhs_module='$module_1'))";
61 if(!empty($relationship_name) && isset($relationship_name)){
62 $query .= " AND relationship_name = '$relationship_name'";
64 $result = $rs->db->query($query);
66 return $rs->db->fetchByAssoc($result);
73 * retireves relationships between two modules
74 * This will return all viewable relationships between two modules
75 * module_query is a filter on the first module
76 * related_module_query is a filter on the second module
77 * relationship_query is a filter on the relationship between them
78 * show_deleted is if deleted items should be shown or not
81 function retrieve_relationships($module_name, $related_module, $relationship_query, $show_deleted, $offset, $max_results){
82 global $beanList, $beanFiles, $dictionary, $current_user;
83 $error = new SoapError();
84 $result_list = array();
85 if(empty($beanList[$module_name]) || empty($beanList[$related_module])){
87 $error->set_error('no_module');
88 return array('result'=>$result_list, 'error'=>$error->get_soap_array());
91 $result = retrieve_relationship_query($module_name, $related_module, $relationship_query, $show_deleted, $offset, $max_results);
93 if(empty($result['module_1'])){
95 $error->set_error('no_relationship_support');
96 return array('result'=>$result_list, 'error'=>$error->get_soap_array());
98 $query = $result['query'];
99 $module_1 = $result['module_1'];
100 $table = $result['join_table'];
102 $class_name = $beanList[$module_1];
103 require_once($beanFiles[$class_name]);
104 $mod = new $class_name();
106 $count_query = str_replace('rt.*', 'count(*)', $query);
107 $result = $mod->db->query($count_query);
108 $row = $mod->db->fetchByAssoc($result);
109 $total_count = $row['count(*)'];
111 if($max_results != '-99'){
112 $result = $mod->db->limitQuery($query, $offset, $max_results);
114 $result = $mod->db->query($query);
116 while($row = $mod->db->fetchByAssoc($result)){
118 $result_list[] = $row;
121 return array('table_name'=>$table, 'result'=>$result_list, 'total_count'=>$total_count, 'error'=>$error->get_soap_array());
125 * retireves modified relationships between two modules
126 * This will return all viewable relationships between two modules
127 * module_query is a filter on the first module
128 * related_module_query is a filter on the second module
129 * relationship_query is a filter on the relationship between them
130 * show_deleted is if deleted items should be shown or not
133 function retrieve_modified_relationships($module_name, $related_module, $relationship_query, $show_deleted, $offset, $max_results, $select_fields = array(), $relationship_name = ''){
134 global $beanList, $beanFiles, $dictionary, $current_user;
135 $error = new SoapError();
136 $result_list = array();
137 if(empty($beanList[$module_name]) || empty($beanList[$related_module])){
139 $error->set_error('no_module');
140 return array('result'=>$result_list, 'error'=>$error->get_soap_array());
143 $row = retrieve_relationships_properties($module_name, $related_module, $relationship_name);
147 $error->set_error('no_relationship_support');
148 return array('result'=>$result_list, 'error'=>$error->get_soap_array());
151 $table = $row['join_table'];
154 //return array('table_name'=>$table, 'result'=>$result_list, 'error'=>$error->get_soap_array());
155 $table = $row['rhs_table'];
156 $module_1 = $row['lhs_module'];
157 $mod_key = $row['lhs_key'];
158 $module_2 = $row['rhs_module'];
159 $mod2_key = $row['rhs_key'];
163 $module_1 = $row['lhs_module'];
164 $mod_key = $row['join_key_lhs'];
165 $module_2 = $row['rhs_module'];
166 $mod2_key = $row['join_key_rhs'];
171 $class_name = $beanList[$module_1];
172 require_once($beanFiles[$class_name]);
173 $mod = new $class_name();
175 $mod2_name = $beanList[$module_2];
176 require_once($beanFiles[$mod2_name]);
177 $mod2 = new $mod2_name();
179 if($has_join == false){
183 if(isset($select_fields) && !empty($select_fields)){
186 foreach($select_fields as $field){
188 $field_select .= "DISTINCT m1.".$field;
191 if(strpos($field, ".") == false){
192 $field_select .= "m1.".$field;
195 $field_select .= $field;
198 if($index < (count($select_fields) - 1))
200 $field_select .= ",";
204 $query = "SELECT $field_select FROM $table $table_alias ";
207 $query = "SELECT rt.* FROM $table $table_alias ";
210 if($has_join == false){
211 $query .= " inner join $mod->table_name m2 on $table_alias.$mod2_key = m2.id ";
214 $query .= " inner join $mod->table_name m1 on rt.$mod_key = m1.id ";
215 $query .= " inner join $mod2->table_name m2 on rt.$mod2_key = m2.id AND m2.id = '$current_user->id'";
218 if(!empty($relationship_query)){
219 $query .= ' WHERE ' . string_format($relationship_query, array($table_alias));
222 if($max_results != '-99'){
223 $result = $mod->db->limitQuery($query, $offset, $max_results);
225 $result = $mod->db->query($query);
227 while($row = $mod->db->fetchByAssoc($result)){
228 $result_list[] = $row;
231 return array('table_name'=>$table, 'result'=>$result_list, 'total_count'=>$total_count, 'error'=>$error->get_soap_array());
235 * retireves relationships between two modules
236 * This will return all viewable relationships between two modules
237 * module_query is a filter on the first module
238 * related_module_query is a filter on the second module
239 * relationship_query is a filter on the relationship between them
240 * show_deleted is if deleted items should be shown or not
243 function clear_relationships($module_name, $related_module){
244 global $beanList, $beanFiles, $dictionary;
245 $result_list = array();
246 if(empty($beanList[$module_name]) || empty($beanList[$related_module])){
252 $row = retrieve_relationships_properties($module_name, $related_module);
258 if($module_name == $row['lhs_module']){
259 $module_1 = $module_name;
260 $module_2 = $related_module;
262 $module_2 = $module_name;
263 $module_1 = $related_module;
265 $table = $row['join_table'];
266 $class_name = $beanList[$module_1];
267 require_once($beanFiles[$class_name]);
268 $mod = new $class_name();
269 $clear_query = "DELTE * FROM $table WHERE 1=1";
270 $result = $mod->db->query($clear_query);
274 function server_save_relationships($list, $from_date, $to_date){
275 require_once('include/utils/db_utils.php');
276 global $beanList, $beanFiles;
277 $from_date = db_convert("'".$from_date."'", 'datetime');
278 $to_date = db_convert("'".$to_date."'", 'datetime');
279 global $sugar_config;
280 $db = DBManagerFactory::getInstance();
287 foreach($list as $record)
297 $table_name = $record['module_name'];
300 foreach($record['name_value_list'] as $name_value){
301 $name = $name_value['name'];
303 if($name == 'date_modified'){
306 $value = db_convert("'".$name_value['value'] . "'", 'varchar');
308 if($name != 'resolve'){
310 $insert = '(' .$name;
311 $insert_values = '(' .$value;
312 if($name != 'date_modified' && $name != 'id' ){
313 $select_values = $name ."=$value";
316 $update = $name ."=$value";
319 $insert .= ', ' .$name;
320 $insert_values .= ', ' .$value;
322 $update .= $name."=$value";
324 $update .= ','.$name."=$value";
327 if($name != 'date_modified' && $name != 'id' ){
328 if(empty($select_values)){
329 $select_values = $name ."=$value";
331 $select_values .= ' AND '.$name ."=$value";
343 //ignore resolve for now server always wins
345 $insert = "INSERT INTO $table_name $insert) VALUES $insert_values)";
346 $update = "UPDATE $table_name SET $update WHERE id=";
347 $delete = "DELETE FROM $table_name WHERE id=";
348 $select_by_id_date = "SELECT id FROM $table_name WHERE id ='$id' AND date_modified > $from_date AND date_modified<= $to_date";
349 $select_by_id = "SELECT id FROM $table_name WHERE id ='$id'";
350 $select_by_values = "SELECT id FROM $table_name WHERE $select_values";
354 $result = $db->query($select_by_id_date);
355 //see if we have a matching id in the date_range
356 if(!($row = $db->fetchByAssoc($result))){
357 //if not lets check if we have one that matches the values
359 $result = $db->query($select_by_values);
360 if(!($row = $db->fetchByAssoc($result))){
362 $result = $db->query($select_by_id);
363 if($row = $db->fetchByAssoc($result)){
365 $db->query($update ."'".$row['id']."'" );
377 return array('add'=>$add, 'modify'=>$modify, 'ids'=>$ids);
382 * gets the from statement from a query without the order by and without the select
385 function get_from_statement($query){
386 $query = explode('FROM', $query);
387 if(sizeof($query) == 1){
388 $query = explode('from', $query[0]);
390 $query = explode( 'ORDER BY',$query[1]);
392 return ' FROM ' . $query[0];
396 function retrieve_relationship_query($module_name, $related_module, $relationship_query, $show_deleted, $offset, $max_results){
397 global $beanList, $beanFiles, $dictionary, $current_user;
398 $error = new SoapError();
399 $result_list = array();
400 if(empty($beanList[$module_name]) || empty($beanList[$related_module])){
402 $error->set_error('no_module');
403 return array('query' =>"", 'module_1'=>"", 'join_table' =>"", 'error'=>$error->get_soap_array());
406 $row = retrieve_relationships_properties($module_name, $related_module);
409 $error->set_error('no_relationship_support');
410 return array('query' =>"", 'module_1'=>"", 'join_table' =>"", 'error'=>$error->get_soap_array());
413 $module_1 = $row['lhs_module'];
414 $mod_key = $row['join_key_lhs'];
415 $module_2 = $row['rhs_module'];
416 $mod2_key = $row['join_key_rhs'];
418 $table = $row['join_table'];
420 return array('query' =>"", 'module_1'=>"", 'join_table' =>"", 'error'=>$error->get_soap_array());
422 $class_name = $beanList[$module_1];
423 require_once($beanFiles[$class_name]);
424 $mod = new $class_name();
426 $mod2_name = $beanList[$module_2];
427 require_once($beanFiles[$mod2_name]);
428 $mod2 = new $mod2_name();
429 $query = "SELECT rt.* FROM $table rt ";
430 $query .= " inner join $mod->table_name m1 on rt.$mod_key = m1.id ";
431 $query .= " inner join $mod2->table_name m2 on rt.$mod2_key = m2.id ";
434 if(!empty($relationship_query)){
435 $query .= ' WHERE ' . $relationship_query;
438 return array('query' =>$query, 'module_1'=>$module_1, 'join_table' => $table, 'error'=>$error->get_soap_array());
441 // Returns name of 'link' field between two given modules
442 function get_module_link_field($module_1, $module_2) {
443 global $beanList, $beanFiles;
445 // check to make sure both modules exist
446 if (empty($beanList[$module_1]) || empty($beanList[$module_2])) {
450 $class_1 = $beanList[$module_1];
451 require_once($beanFiles[$class_1]);
453 $obj_1 = new $class_1();
455 // loop through link fields of $module_1, checking for a link to $module_2
456 foreach ($obj_1->get_linked_fields() as $linked_field) {
457 $obj_1->load_relationship($linked_field['name']);
458 $field = $linked_field['name'];
460 if (empty($obj_1->$field)) {
464 if ($obj_1->$field->getRelatedModuleName() == $module_2) {
472 // Retrieves array of ids for records of $get_module linked to $from_module by $get_id
473 // Example: to retrieve list of Contacts associated to Account X: $return = get_linked_records("Contacts", "Accounts", "X");
474 function get_linked_records($get_module, $from_module, $get_id) {
475 global $beanList, $beanFiles;
477 // instantiate and retrieve $from_module
478 $from_class = $beanList[$from_module];
479 require_once($beanFiles[$from_class]);
480 $from_mod = new $from_class();
481 $from_mod->retrieve($get_id);
483 $field = get_module_link_field($from_module, $get_module);
484 if ($field === FALSE) {
488 $from_mod->load_relationship($field);
489 $id_arr = $from_mod->$field->get();
492 if ($get_module == 'EmailAddresses') {
493 $emails = $from_mod->emailAddress->addresses;
494 $email_arr = array();
495 foreach ($emails as $email) {
496 $email_arr[] = $email['email_address_id'];