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 = '".$rs->db->quote($module_1)."' AND rhs_module='".$rs->db->quote($module_2)."') OR (lhs_module = '".$rs->db->quote($module_2)."' AND rhs_module='".$rs->db->quote($module_1)."'))";
  61         if(!empty($relationship_name) && isset($relationship_name)){
  62                 $query .= " AND relationship_name = '".$rs->db->quote($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
  84         $error = new SoapError();
  85         $result_list = array();
  86         if(empty($beanList[$module_name]) || empty($beanList[$related_module])){
  87
  88                 $error->set_error('no_module');
  89                 return array('result'=>$result_list, 'error'=>$error->get_soap_array());
  90         }
  91
  92         $result = retrieve_relationship_query($module_name,  $related_module, $relationship_query, $show_deleted, $offset, $max_results);
  93
  94         if(empty($result['module_1'])){
  95
  96                 $error->set_error('no_relationship_support');
  97                 return array('result'=>$result_list, 'error'=>$error->get_soap_array());
  98         }
  99         $query = $result['query'];
 100         $module_1 = $result['module_1'];
 101         $table = $result['join_table'];
 102
 103         $class_name = $beanList[$module_1];
 104         require_once($beanFiles[$class_name]);
 105         $mod = new $class_name();
 106
 107         $count_query = str_replace('rt.*', 'count(*)', $query);
 108         $result = $mod->db->query($count_query);
 109         $row = $mod->db->fetchByAssoc($result);
 110         $total_count = $row['count(*)'];
 111
 112         if($max_results != '-99'){
 113                 $result = $mod->db->limitQuery($query, $offset, $max_results);
 114         }else{
 115                 $result = $mod->db->query($query);
 116         }
 117         while($row = $mod->db->fetchByAssoc($result)){
 118
 119                 $result_list[] = $row;
 120         }
 121
 122         return array('table_name'=>$table, 'result'=>$result_list, 'total_count'=>$total_count, 'error'=>$error->get_soap_array());
 123 }
 124
 125 /*
 126  * retrieve_modified_relationships
 127  *
 128  * This method retrieves modified relationships between two modules
 129  * This will return all viewable relationships between two modules
 130  *
 131  * @param $module_name String value of the module on the left hand side of relationship
 132  * @param related_module String value of the module on the right hand side of relationship
 133  * @param relationship_query SQL String used to query for the relationships
 134  * @show_deleted boolean value indicating whether or not deleted items should be shown (IGNORED)
 135  * @offset integer value indicating the starting offset of results to return
 136  * @max_results integer value indicating the maximum number of results to return
 137  * @select_fields Mixed Array indicating the select fields used in the query to return in results
 138  * @relationship_name String value of the relationship name as defined in the relationships table to be used in retrieving the relationship information
 139  * @return Mixed Array of results with the following delta/value information:
 140  *         table_name String value of the table name queried for the results
 141  *         result Mixed Array of the results.  Each entry in the Array contains an Array of key/value pairs from the select_fields parameter
 142  *         total_count integer value indicating the total count of results from the query
 143  *         error Mixed Array containing the SOAP errors if found, empty otherwise
 144  *
 145  */
 146 function retrieve_modified_relationships($module_name, $related_module, $relationship_query, $show_deleted, $offset, $max_results, $select_fields = array(), $relationship_name = ''){
 147
 148     global  $beanList, $beanFiles, $dictionary, $current_user;
 149         $error = new SoapError();
 150         $result_list = array();
 151         if(empty($beanList[$module_name]) || empty($beanList[$related_module])){
 152
 153                 $error->set_error('no_module');
 154                 return array('result'=>$result_list, 'error'=>$error->get_soap_array());
 155         }
 156
 157         $row = retrieve_relationships_properties($module_name, $related_module, $relationship_name);
 158
 159         if(empty($row)){
 160
 161                 $error->set_error('no_relationship_support');
 162                 return array('result'=>$result_list, 'error'=>$error->get_soap_array());
 163         }
 164
 165         $table = $row['join_table'];
 166         $has_join = true;
 167         if(empty($table)){
 168                 //return array('table_name'=>$table, 'result'=>$result_list, 'error'=>$error->get_soap_array());
 169                 $table = $row['rhs_table'];
 170                 $module_1 = $row['lhs_module'];
 171                 $mod_key = $row['lhs_key'];
 172                 $module_2 = $row['rhs_module'];
 173                 $mod2_key = $row['rhs_key'];
 174                 $has_join = false;
 175         }
 176         else{
 177                 $module_1 = $row['lhs_module'];
 178                 $mod_key = $row['join_key_lhs'];
 179                 $module_2 = $row['rhs_module'];
 180                 $mod2_key = $row['join_key_rhs'];
 181         }
 182
 183
 184
 185         $class_name = $beanList[$module_1];
 186         require_once($beanFiles[$class_name]);
 187         $mod = new $class_name();
 188
 189         $mod2_name = $beanList[$module_2];
 190         require_once($beanFiles[$mod2_name]);
 191         $mod2 = new $mod2_name();
 192         $table_alias = 'rt';
 193         if($has_join == false){
 194                 $table_alias = 'm1';
 195         }
 196
 197         if(isset($select_fields) && !empty($select_fields)){
 198                 $index = 0;
 199                 $field_select ='';
 200
 201                 foreach($select_fields as $field){
 202                         if($field == "id"){
 203                                 $field_select .= "DISTINCT m1.id";
 204                         } else {
 205                             $parts = explode(' ', $field);
 206                             $alias = '';
 207                             if(count($parts) > 1) {
 208                                 // have aliases: something like "blah.blah blah"
 209                     $alias = array_pop($parts);
 210                     $field = array_pop($parts); // will check for . further down
 211                             }
 212                             if($alias == "email1") {
 213                     // special case for primary emails
 214                     $field_select .= "(SELECT email_addresses.email_address FROM {$mod->table_name}
 215                         LEFT JOIN  email_addr_bean_rel ON {$mod->table_name}.id = email_addr_bean_rel.bean_id
 216                                 AND email_addr_bean_rel.bean_module='{$mod->module_dir}'
 217                                 AND email_addr_bean_rel.deleted=0 AND email_addr_bean_rel.primary_address=1
 218                         LEFT JOIN email_addresses ON email_addresses.id = email_addr_bean_rel.email_address_id Where {$mod->table_name}.id = m1.ID) email1";
 219                             } elseif($alias == "email2") {
 220                     // special case for non-primary emails
 221                     // FIXME: This is not a DB-safe code. Does not work on SQL Server & Oracle.
 222                     // Using dirty hack here.
 223                     $field_select .= "(SELECT email_addresses.email_address FROM {$mod->table_name}
 224                         LEFT JOIN  email_addr_bean_rel on {$mod->table_name}.id = email_addr_bean_rel.bean_id
 225                                 AND email_addr_bean_rel.bean_module='{$mod->module_dir}' AND email_addr_bean_rel.deleted=0
 226                                 AND email_addr_bean_rel.primary_address!=1
 227                         LEFT JOIN email_addresses ON email_addresses.id = email_addr_bean_rel.email_address_id Where {$mod->table_name}.id = m1.ID limit 1) email2";
 228                             } else {
 229                     if(strpos($field, ".") == false) {
 230                         // no dot - field for m1
 231                         $fieldname = "m1.".$mod->db->getValidDBName($field);
 232                     } else {
 233                         // There is a dot in here somewhere.
 234                         list($table_part,$field_part) = explode('.',$field);
 235                         $fieldname = $mod->db->getValidDBName($table_part).".".$mod->db->getValidDBName($field_part);
 236                             }
 237                             $field_select .= $fieldname;
 238                             if(!empty($alias)) {
 239                                 $field_select .= " ".$mod->db->getValidDBName($alias);
 240                             }
 241                             }
 242                         }
 243                         if($index < (count($select_fields) - 1))
 244                         {
 245                                 $field_select .= ",";
 246                                 $index++;
 247                         }
 248                 }//end foreach
 249                 $query = "SELECT $field_select FROM $table $table_alias ";
 250         }
 251         else{
 252                 $query = "SELECT rt.* FROM  $table $table_alias ";
 253         }
 254
 255         if($has_join == false){
 256                 $query .= " inner join $mod->table_name m2 on $table_alias.$mod2_key = m2.id AND m2.id = '$current_user->id'";
 257         }
 258         else{
 259                 $query .= " inner join $mod->table_name m1 on rt.$mod_key = m1.id ";
 260                 $query .= " inner join $mod2->table_name m2 on rt.$mod2_key = m2.id AND m2.id = '$current_user->id'";
 261         }
 262
 263         if(!empty($relationship_query)){
 264                 $query .= ' WHERE ' . string_format($relationship_query, array($table_alias));
 265         }
 266
 267         if($max_results != '-99'){
 268                 $result = $mod->db->limitQuery($query, $offset, $max_results);
 269         }else{
 270                 $result = $mod->db->query($query);
 271         }
 272         while($row = $mod->db->fetchByAssoc($result)){
 273                 $result_list[] = $row;
 274         }
 275
 276     $total_count = !empty($result_list) ? count($result_list) : 0;
 277         return array('table_name'=>$table, 'result'=>$result_list, 'total_count'=>$total_count, 'error'=>$error->get_soap_array());
 278 }
 279
 280 function server_save_relationships($list, $from_date, $to_date){
 281         require_once('include/utils/db_utils.php');
 282         global  $beanList, $beanFiles;
 283         $from_date = db_convert("'".$GLOBALS['db']->quote($from_date)."'", 'datetime');
 284         $to_date = db_convert("'".$GLOBALS['db']->quote($to_date)."'", 'datetime');
 285         global $sugar_config;
 286         $db = DBManagerFactory::getInstance();
 287
 288         $ids = array();
 289         $add = 0;
 290         $modify = 0;
 291         $deleted = 0;
 292
 293         foreach($list as $record)
 294         {
 295                 $insert = '';
 296                 $insert_values = '';
 297                 $update = '';
 298                 $select_values  = '';
 299                 $args = array();
 300
 301                 $id = $record['id'];
 302
 303                 $table_name = $record['module_name'];
 304                 $resolve = 1;
 305
 306                 foreach($record['name_value_list'] as $name_value){
 307                         $name = $GLOBALS['db']->quote($name_value['name']);
 308
 309                         if($name == 'date_modified'){
 310                 $value = $to_date;
 311                         }else{
 312                 $value = db_convert("'".$GLOBALS['db']->quote($name_value['value'])."'", 'varchar');
 313                         }
 314                         if($name != 'resolve'){
 315                         if(empty($insert)){
 316                                 $insert = '('   .$name;
 317                                 $insert_values = '('    .$value;
 318                                 if($name != 'date_modified' && $name != 'id' ){
 319                                         $select_values = $name ."=$value";
 320                                 }
 321                                 if($name != 'id'){
 322                                         $update = $name ."=$value";
 323                                 }
 324                         }else{
 325                                 $insert .= ', ' .$name;
 326                                 $insert_values .= ', '  .$value;
 327                                 if(empty($update)){
 328                                         $update .= $name."=$value";
 329                                 }else{
 330                                         $update .= ','.$name."=$value";
 331                                 }
 332
 333                                 if($name != 'date_modified' && $name != 'id' ){
 334                                         if(empty($select_values)){
 335                                                 $select_values = $name ."=$value";
 336                                         }else{
 337                                                 $select_values .= ' AND '.$name ."=$value";
 338                                         }
 339                                 }
 340                         }
 341                         }else{
 342                                 $resolve = $value;
 343                         }
 344
 345
 346
 347
 348                 }
 349                 //ignore resolve for now server always wins
 350                 $resolve = 1;
 351                 $insert = "INSERT INTO $table_name $insert) VALUES $insert_values)";
 352                 $update = "UPDATE $table_name SET $update WHERE id=";
 353                 $delete = "DELETE FROM $table_name WHERE id=";
 354                 $select_by_id_date = "SELECT id FROM $table_name WHERE id ='".$GLOBALS['db']->quote($id)."' AND date_modified > $from_date AND date_modified<= $to_date";
 355                 $select_by_id = "SELECT id FROM $table_name WHERE id ='".$GLOBALS['db']->quote($id)."'";
 356                 $select_by_values = "SELECT id FROM $table_name WHERE $select_values";
 357                 $updated = false;
 358
 359
 360                 $result = $db->query($select_by_id_date);
 361                 //see if we have a matching id in the date_range
 362                 if(!($row = $db->fetchByAssoc($result))){
 363                         //if not lets check if we have one that matches the values
 364
 365                         $result = $db->query($select_by_values);
 366                         if(!($row = $db->fetchByAssoc($result))){
 367
 368                                 $result = $db->query($select_by_id);
 369                                 if($row = $db->fetchByAssoc($result)){
 370
 371                                         $db->query($update ."'".$GLOBALS['db']->quote($row['id'])."'" );
 372                                         $ids[] = $row['id'];
 373                                         $modify++;
 374                                 }else{
 375                                         $db->query($insert);
 376                                         $add++;
 377                                         $ids[] = $row['id'];
 378                                 }
 379                         }
 380         }
 381
 382         }
 383         return array('add'=>$add, 'modify'=>$modify, 'ids'=>$ids);
 384 }
 385
 386 /*
 387  *
 388  * gets the from statement from a query without the order by and without the select
 389  *
 390  */
 391 function get_from_statement($query){
 392         $query = explode('FROM', $query);
 393         if(sizeof($query) == 1){
 394                 $query = explode('from', $query[0]);
 395         }
 396         $query = explode( 'ORDER BY',$query[1]);
 397
 398         return ' FROM ' . $query[0];
 399
 400 }
 401
 402 function retrieve_relationship_query($module_name,  $related_module, $relationship_query, $show_deleted, $offset, $max_results){
 403         global  $beanList, $beanFiles, $dictionary, $current_user;
 404         $error = new SoapError();
 405         $result_list = array();
 406         if(empty($beanList[$module_name]) || empty($beanList[$related_module])){
 407
 408                 $error->set_error('no_module');
 409                 return array('query' =>"", 'module_1'=>"", 'join_table' =>"", 'error'=>$error->get_soap_array());
 410         }
 411
 412         $row = retrieve_relationships_properties($module_name, $related_module);
 413         if(empty($row)){
 414
 415                 $error->set_error('no_relationship_support');
 416                 return array('query' =>"", 'module_1'=>"", 'join_table' =>"", 'error'=>$error->get_soap_array());
 417         }
 418
 419         $module_1 = $row['lhs_module'];
 420         $mod_key = $row['join_key_lhs'];
 421         $module_2 = $row['rhs_module'];
 422         $mod2_key = $row['join_key_rhs'];
 423
 424         $table = $row['join_table'];
 425         if(empty($table)){
 426                 return array('query' =>"", 'module_1'=>"", 'join_table' =>"", 'error'=>$error->get_soap_array());
 427         }
 428         $class_name = $beanList[$module_1];
 429         require_once($beanFiles[$class_name]);
 430         $mod = new $class_name();
 431
 432         $mod2_name = $beanList[$module_2];
 433         require_once($beanFiles[$mod2_name]);
 434         $mod2 = new $mod2_name();
 435         $query = "SELECT rt.* FROM  $table rt ";
 436         $query .= " inner join $mod->table_name m1 on rt.$mod_key = m1.id ";
 437         $query .= " inner join $mod2->table_name m2 on rt.$mod2_key = m2.id  ";
 438
 439
 440         if(!empty($relationship_query)){
 441                 $query .= ' WHERE ' . $relationship_query;
 442         }
 443
 444         return array('query' =>$query, 'module_1'=>$module_1, 'join_table' => $table, 'error'=>$error->get_soap_array());
 445 }
 446
 447 // Returns name of 'link' field between two given modules
 448 function get_module_link_field($module_1, $module_2) {
 449         global $beanList, $beanFiles;
 450
 451         // check to make sure both modules exist
 452         if (empty($beanList[$module_1]) || empty($beanList[$module_2])) {
 453                 return FALSE;
 454         }
 455
 456         $class_1 = $beanList[$module_1];
 457         require_once($beanFiles[$class_1]);
 458
 459         $obj_1 = new $class_1();
 460
 461         // loop through link fields of $module_1, checking for a link to $module_2
 462         foreach ($obj_1->get_linked_fields() as $linked_field) {
 463                 $obj_1->load_relationship($linked_field['name']);
 464                 $field = $linked_field['name'];
 465
 466                 if (empty($obj_1->$field)) {
 467                         continue;
 468                 }
 469
 470                 if ($obj_1->$field->getRelatedModuleName() == $module_2) {
 471                         return $field;
 472                 }
 473         }
 474
 475         return FALSE;
 476 }
 477
 478 // Retrieves array of ids for records of $get_module linked to $from_module by $get_id
 479 // Example: to retrieve list of Contacts associated to Account X: $return = get_linked_records("Contacts", "Accounts", "X");
 480 function get_linked_records($get_module, $from_module, $get_id) {
 481         global $beanList, $beanFiles;
 482
 483         // instantiate and retrieve $from_module
 484         $from_class = $beanList[$from_module];
 485         require_once($beanFiles[$from_class]);
 486         $from_mod = new $from_class();
 487         $from_mod->retrieve($get_id);
 488
 489         $field = get_module_link_field($from_module, $get_module);
 490         if ($field === FALSE) {
 491                 return FALSE;
 492         }
 493
 494         $from_mod->load_relationship($field);
 495         $id_arr = $from_mod->$field->get();
 496
 497         //bug: 38065
 498         if ($get_module == 'EmailAddresses') {
 499                 $emails = $from_mod->emailAddress->addresses;
 500                 $email_arr = array();
 501                 foreach ($emails as $email) {
 502                         $email_arr[] = $email['email_address_id'];
 503                 }
 504                 return $email_arr;
 505         }
 506
 507         return $id_arr;
 508 }
 509
 510 ?>