soap/SoapRelationshipHelper.php

   1 <?php
   2
   3
   4 if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');
   5 /*********************************************************************************
   6  * SugarCRM Community Edition is a customer relationship management program developed by
   7  * SugarCRM, Inc. Copyright (C) 2004-2011 SugarCRM Inc.
   8  *
   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.
  15  *
  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
  19  * details.
  20  *
  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
  24  * 02110-1301 USA.
  25  *
  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.
  28  *
  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.
  32  *
  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  ********************************************************************************/
  39
  40 require_once('soap/SoapError.php');
  41
  42 function check_for_relationship($relationships, $module){
  43         foreach($relationships as $table=>$rel){
  44                 if( $rel['rhs_key'] == $module){
  45                         return $table;
  46
  47                 }
  48         }
  49         return false;
  50 }
  51
  52 /*
  53  * takes in two modules and returns the relationship information about them
  54  *
  55  */
  56
  57 function retrieve_relationships_properties($module_1, $module_2, $relationship_name = ""){
  58
  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'";
  63         }
  64         $result = $rs->db->query($query);
  65
  66         return $rs->db->fetchByAssoc($result);
  67 }
  68
  69
  70
  71
  72 /*
  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
  79  *
  80  */
  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])){
  86
  87                 $error->set_error('no_module');
  88                 return array('result'=>$result_list, 'error'=>$error->get_soap_array());
  89         }
  90
  91         $result = retrieve_relationship_query($module_name,  $related_module, $relationship_query, $show_deleted, $offset, $max_results);
  92
  93         if(empty($result['module_1'])){
  94
  95                 $error->set_error('no_relationship_support');
  96                 return array('result'=>$result_list, 'error'=>$error->get_soap_array());
  97         }
  98         $query = $result['query'];
  99         $module_1 = $result['module_1'];
 100         $table = $result['join_table'];
 101
 102         $class_name = $beanList[$module_1];
 103         require_once($beanFiles[$class_name]);
 104         $mod = new $class_name();
 105
 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(*)'];
 110
 111         if($max_results != '-99'){
 112                 $result = $mod->db->limitQuery($query, $offset, $max_results);
 113         }else{
 114                 $result = $mod->db->query($query);
 115         }
 116         while($row = $mod->db->fetchByAssoc($result)){
 117
 118                 $result_list[] = $row;
 119         }
 120
 121         return array('table_name'=>$table, 'result'=>$result_list, 'total_count'=>$total_count, 'error'=>$error->get_soap_array());
 122 }
 123
 124 /*
 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
 131  *
 132  */
 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])){
 138
 139                 $error->set_error('no_module');
 140                 return array('result'=>$result_list, 'error'=>$error->get_soap_array());
 141         }
 142
 143         $row = retrieve_relationships_properties($module_name, $related_module, $relationship_name);
 144
 145         if(empty($row)){
 146
 147                 $error->set_error('no_relationship_support');
 148                 return array('result'=>$result_list, 'error'=>$error->get_soap_array());
 149         }
 150
 151         $table = $row['join_table'];
 152         $has_join = true;
 153         if(empty($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'];
 160                 $has_join = false;
 161         }
 162         else{
 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'];
 167         }
 168
 169
 170
 171         $class_name = $beanList[$module_1];
 172         require_once($beanFiles[$class_name]);
 173         $mod = new $class_name();
 174
 175         $mod2_name = $beanList[$module_2];
 176         require_once($beanFiles[$mod2_name]);
 177         $mod2 = new $mod2_name();
 178         $table_alias = 'rt';
 179         if($has_join == false){
 180                 $table_alias = 'm1';
 181         }
 182
 183         if(isset($select_fields) && !empty($select_fields)){
 184                 $index = 0;
 185                 $field_select ='';
 186                 foreach($select_fields as $field){
 187                         if($field == "id"){
 188                                 $field_select .= "DISTINCT m1.".$field;
 189                         }
 190                         else{
 191                                 if(strpos($field, ".") == false){
 192                                         $field_select .= "m1.".$field;
 193                                 }
 194                                 else{
 195                                         $field_select .= $field;
 196                                 }
 197                         }
 198                         if($index < (count($select_fields) - 1))
 199                         {
 200                                 $field_select .= ",";
 201                                 $index++;
 202                         }
 203                 }//end foreach
 204                 $query = "SELECT $field_select FROM $table $table_alias ";
 205         }
 206         else{
 207                 $query = "SELECT rt.* FROM  $table $table_alias ";
 208         }
 209
 210         if($has_join == false){
 211                 $query .= " inner join $mod->table_name m2 on $table_alias.$mod2_key = m2.id AND m2.id = '$current_user->id'";
 212         }
 213         else{
 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'";
 216         }
 217
 218         if(!empty($relationship_query)){
 219                 $query .= ' WHERE ' . string_format($relationship_query, array($table_alias));
 220         }
 221
 222         if($max_results != '-99'){
 223                 $result = $mod->db->limitQuery($query, $offset, $max_results);
 224         }else{
 225                 $result = $mod->db->query($query);
 226         }
 227         while($row = $mod->db->fetchByAssoc($result)){
 228                 $result_list[] = $row;
 229         }
 230
 231         return array('table_name'=>$table, 'result'=>$result_list, 'total_count'=>$total_count, 'error'=>$error->get_soap_array());
 232 }
 233
 234 /*
 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
 241  *
 242  */
 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])){
 247
 248
 249                 return false;
 250         }
 251
 252         $row = retrieve_relationships_properties($module_name, $related_module);
 253         if(empty($row)){
 254
 255                 return false;
 256         }
 257
 258         if($module_name == $row['lhs_module']){
 259                 $module_1 = $module_name;
 260                 $module_2 = $related_module;
 261         }else{
 262                 $module_2 = $module_name;
 263                 $module_1 = $related_module;
 264         }
 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);
 271         return true;
 272 }
 273
 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();
 281
 282         $ids = array();
 283         $add = 0;
 284         $modify = 0;
 285         $deleted = 0;
 286
 287         foreach($list as $record)
 288         {
 289                 $insert = '';
 290                 $insert_values = '';
 291                 $update = '';
 292                 $select_values  = '';
 293                 $args = array();
 294
 295                 $id = $record['id'];
 296
 297                 $table_name = $record['module_name'];
 298                 $resolve = 1;
 299
 300                 foreach($record['name_value_list'] as $name_value){
 301                         $name = $name_value['name'];
 302
 303                         if($name == 'date_modified'){
 304                                         $value = $to_date;
 305                         }else{
 306                                         $value = db_convert("'".$name_value['value'] . "'", 'varchar');
 307                         }
 308                         if($name != 'resolve'){
 309                         if(empty($insert)){
 310                                 $insert = '('   .$name;
 311                                 $insert_values = '('    .$value;
 312                                 if($name != 'date_modified' && $name != 'id' ){
 313                                         $select_values = $name ."=$value";
 314                                 }
 315                                 if($name != 'id'){
 316                                         $update = $name ."=$value";
 317                                 }
 318                         }else{
 319                                 $insert .= ', ' .$name;
 320                                 $insert_values .= ', '  .$value;
 321                                 if(empty($update)){
 322                                         $update .= $name."=$value";
 323                                 }else{
 324                                         $update .= ','.$name."=$value";
 325                                 }
 326
 327                                 if($name != 'date_modified' && $name != 'id' ){
 328                                         if(empty($select_values)){
 329                                                 $select_values = $name ."=$value";
 330                                         }else{
 331                                                 $select_values .= ' AND '.$name ."=$value";
 332                                         }
 333                                 }
 334                         }
 335                         }else{
 336                                 $resolve = $value;
 337                         }
 338
 339
 340
 341
 342                 }
 343                 //ignore resolve for now server always wins
 344                 $resolve = 1;
 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";
 351                 $updated = false;
 352
 353
 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
 358
 359                         $result = $db->query($select_by_values);
 360                         if(!($row = $db->fetchByAssoc($result))){
 361
 362                                 $result = $db->query($select_by_id);
 363                                 if($row = $db->fetchByAssoc($result)){
 364
 365                                         $db->query($update ."'".$row['id']."'" );
 366                                         $ids[] = $row['id'];
 367                                         $modify++;
 368                                 }else{
 369                                         $db->query($insert);
 370                                         $add++;
 371                                         $ids[] = $row['id'];
 372                                 }
 373                         }
 374         }
 375
 376         }
 377         return array('add'=>$add, 'modify'=>$modify, 'ids'=>$ids);
 378 }
 379
 380 /*
 381  *
 382  * gets the from statement from a query without the order by and without the select
 383  *
 384  */
 385 function get_from_statement($query){
 386         $query = explode('FROM', $query);
 387         if(sizeof($query) == 1){
 388                 $query = explode('from', $query[0]);
 389         }
 390         $query = explode( 'ORDER BY',$query[1]);
 391
 392         return ' FROM ' . $query[0];
 393
 394 }
 395
 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])){
 401
 402                 $error->set_error('no_module');
 403                 return array('query' =>"", 'module_1'=>"", 'join_table' =>"", 'error'=>$error->get_soap_array());
 404         }
 405
 406         $row = retrieve_relationships_properties($module_name, $related_module);
 407         if(empty($row)){
 408
 409                 $error->set_error('no_relationship_support');
 410                 return array('query' =>"", 'module_1'=>"", 'join_table' =>"", 'error'=>$error->get_soap_array());
 411         }
 412
 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'];
 417
 418         $table = $row['join_table'];
 419         if(empty($table)){
 420                 return array('query' =>"", 'module_1'=>"", 'join_table' =>"", 'error'=>$error->get_soap_array());
 421         }
 422         $class_name = $beanList[$module_1];
 423         require_once($beanFiles[$class_name]);
 424         $mod = new $class_name();
 425
 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  ";
 432
 433
 434         if(!empty($relationship_query)){
 435                 $query .= ' WHERE ' . $relationship_query;
 436         }
 437
 438         return array('query' =>$query, 'module_1'=>$module_1, 'join_table' => $table, 'error'=>$error->get_soap_array());
 439 }
 440
 441 // Returns name of 'link' field between two given modules
 442 function get_module_link_field($module_1, $module_2) {
 443         global $beanList, $beanFiles;
 444
 445         // check to make sure both modules exist
 446         if (empty($beanList[$module_1]) || empty($beanList[$module_2])) {
 447                 return FALSE;
 448         }
 449
 450         $class_1 = $beanList[$module_1];
 451         require_once($beanFiles[$class_1]);
 452
 453         $obj_1 = new $class_1();
 454
 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'];
 459
 460                 if (empty($obj_1->$field)) {
 461                         continue;
 462                 }
 463
 464                 if ($obj_1->$field->getRelatedModuleName() == $module_2) {
 465                         return $field;
 466                 }
 467         }
 468
 469         return FALSE;
 470 }
 471
 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;
 476
 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);
 482
 483         $field = get_module_link_field($from_module, $get_module);
 484         if ($field === FALSE) {
 485                 return FALSE;
 486         }
 487
 488         $from_mod->load_relationship($field);
 489         $id_arr = $from_mod->$field->get();
 490
 491         //bug: 38065
 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'];
 497                 }
 498                 return $email_arr;
 499         }
 500
 501         return $id_arr;
 502 }
 503
 504 ?>