modules/ProspectLists/ProspectList.php

   1 <?php
   2 if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');
   3 /*********************************************************************************
   4  * SugarCRM Community Edition is a customer relationship management program developed by
   5  * SugarCRM, Inc. Copyright (C) 2004-2012 SugarCRM Inc.
   6  *
   7  * This program is free software; you can redistribute it and/or modify it under
   8  * the terms of the GNU Affero General Public License version 3 as published by the
   9  * Free Software Foundation with the addition of the following permission added
  10  * to Section 15 as permitted in Section 7(a): FOR ANY PART OF THE COVERED WORK
  11  * IN WHICH THE COPYRIGHT IS OWNED BY SUGARCRM, SUGARCRM DISCLAIMS THE WARRANTY
  12  * OF NON INFRINGEMENT OF THIRD PARTY RIGHTS.
  13  *
  14  * This program is distributed in the hope that it will be useful, but WITHOUT
  15  * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
  16  * FOR A PARTICULAR PURPOSE.  See the GNU Affero General Public License for more
  17  * details.
  18  *
  19  * You should have received a copy of the GNU Affero General Public License along with
  20  * this program; if not, see http://www.gnu.org/licenses or write to the Free
  21  * Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
  22  * 02110-1301 USA.
  23  *
  24  * You can contact SugarCRM, Inc. headquarters at 10050 North Wolfe Road,
  25  * SW2-130, Cupertino, CA 95014, USA. or at email address contact@sugarcrm.com.
  26  *
  27  * The interactive user interfaces in modified source and object code versions
  28  * of this program must display Appropriate Legal Notices, as required under
  29  * Section 5 of the GNU Affero General Public License version 3.
  30  *
  31  * In accordance with Section 7(b) of the GNU Affero General Public License version 3,
  32  * these Appropriate Legal Notices must retain the display of the "Powered by
  33  * SugarCRM" logo. If the display of the logo is not reasonably feasible for
  34  * technical reasons, the Appropriate Legal Notices must display the words
  35  * "Powered by SugarCRM".
  36  ********************************************************************************/
  37
  38 /*********************************************************************************
  39
  40  * Description:
  41  ********************************************************************************/
  42
  43
  44
  45
  46
  47
  48
  49 class ProspectList extends SugarBean {
  50         var $field_name_map;
  51
  52         // Stored fields
  53         var $id;
  54         var $date_entered;
  55         var $date_modified;
  56         var $modified_user_id;
  57         var $assigned_user_id;
  58         var $created_by;
  59         var $created_by_name;
  60         var $modified_by_name;
  61         var $list_type;
  62         var $domain_name;
  63
  64         var $name;
  65         var $description;
  66
  67         // These are related
  68         var $assigned_user_name;
  69         var $prospect_id;
  70         var $contact_id;
  71         var $lead_id;
  72
  73         // module name definitions and table relations
  74         var $table_name = "prospect_lists";
  75         var $module_dir = 'ProspectLists';
  76         var $rel_prospects_table = "prospect_lists_prospects";
  77         var $object_name = "ProspectList";
  78
  79         // This is used to retrieve related fields from form posts.
  80         var $additional_column_fields = array(
  81                 'assigned_user_name', 'assigned_user_id', 'campaign_id',
  82         );
  83         var $relationship_fields = array(
  84                 'campaign_id'=>'campaigns',
  85                 'prospect_list_prospects' => 'prospects',
  86         );
  87
  88     var $entry_count;
  89
  90         function ProspectList() {
  91                 global $sugar_config;
  92                 parent::SugarBean();
  93
  94         }
  95
  96         var $new_schema = true;
  97
  98         function get_summary_text()
  99         {
 100                 return "$this->name";
 101         }
 102
 103         function create_list_query($order_by, $where, $show_deleted = 0)
 104         {
 105                 $custom_join = $this->custom_fields->getJOIN();
 106
 107                 $query = "SELECT ";
 108                 $query .= "users.user_name as assigned_user_name, ";
 109                 $query .= "prospect_lists.*";
 110
 111                 if($custom_join){
 112                         $query .= $custom_join['select'];
 113                 }
 114                 $query .= " FROM prospect_lists ";
 115
 116                 $query .= "LEFT JOIN users
 117                                         ON prospect_lists.assigned_user_id=users.id ";
 118
 119                 if($custom_join){
 120                         $query .= $custom_join['join'];
 121                 }
 122
 123                         $where_auto = '1=1';
 124                                 if($show_deleted == 0){
 125                         $where_auto = "$this->table_name.deleted=0";
 126                                 }else if($show_deleted == 1){
 127                         $where_auto = "$this->table_name.deleted=1";
 128                                 }
 129
 130                 if($where != "")
 131                         $query .= "where $where AND ".$where_auto;
 132                 else
 133                         $query .= "where ".$where_auto;
 134
 135                 if($order_by != "")
 136                         $query .= " ORDER BY $order_by";
 137                 else
 138                         $query .= " ORDER BY prospect_lists.name";
 139
 140                 return $query;
 141         }
 142
 143
 144         function create_export_query($order_by, $where)
 145         {
 146
 147                                 $query = "SELECT
 148                                 prospect_lists.*,
 149                                 users.user_name as assigned_user_name ";
 150                                     $query .= "FROM prospect_lists ";
 151                 $query .=                               "LEFT JOIN users
 152                                 ON prospect_lists.assigned_user_id=users.id ";
 153
 154                 $where_auto = " prospect_lists.deleted=0";
 155
 156         if($where != "")
 157                 $query .= " WHERE $where AND ".$where_auto;
 158         else
 159                 $query .= " WHERE ".$where_auto;
 160
 161         if($order_by != "")
 162                 $query .= " ORDER BY $order_by";
 163         else
 164                 $query .= " ORDER BY prospect_lists.name";
 165         return $query;
 166     }
 167
 168         function create_export_members_query($record_id)
 169         {
 170                 $leads_query = "SELECT l.id AS id, 'Leads' AS related_type, '' AS \"name\", l.first_name AS first_name, l.last_name AS last_name, l.title AS title, l.salutation AS salutation,
 171                                 l.primary_address_street AS primary_address_street,l.primary_address_city AS primary_address_city, l.primary_address_state AS primary_address_state, l.primary_address_postalcode AS primary_address_postalcode, l.primary_address_country AS primary_address_country,
 172                                 l.account_name AS account_name,
 173                                 ea.email_address AS primary_email_address, ea.invalid_email AS invalid_email, ea.opt_out AS opt_out, ea.deleted AS ea_deleted, ear.deleted AS ear_deleted, ear.primary_address AS primary_address,
 174                                 l.do_not_call AS do_not_call, l.phone_fax AS phone_fax, l.phone_other AS phone_other, l.phone_home AS phone_home, l.phone_mobile AS phone_mobile, l.phone_work AS phone_work
 175                                 FROM prospect_lists_prospects plp
 176                                 INNER JOIN leads l ON plp.related_id=l.id
 177                                 LEFT JOIN email_addr_bean_rel ear ON  ear.bean_id=l.id AND ear.deleted=0
 178                                 LEFT JOIN email_addresses ea ON ear.email_address_id=ea.id
 179                                 WHERE plp.prospect_list_id = $record_id AND plp.deleted=0
 180                                 AND l.deleted=0
 181                                 AND (ear.deleted=0 OR ear.deleted IS NULL)";
 182
 183                 $users_query = "SELECT u.id AS id, 'Users' AS related_type, '' AS \"name\", u.first_name AS first_name, u.last_name AS last_name,u.title AS title, '' AS salutation,
 184                                 u.address_street AS primary_address_street,u.address_city AS primary_address_city, u.address_state AS primary_address_state,  u.address_postalcode AS primary_address_postalcode, u.address_country AS primary_address_country,
 185                                 '' AS account_name,
 186                                 ea.email_address AS email_address, ea.invalid_email AS invalid_email, ea.opt_out AS opt_out, ea.deleted AS ea_deleted, ear.deleted AS ear_deleted, ear.primary_address AS primary_address,
 187                                 0 AS do_not_call, u.phone_fax AS phone_fax, u.phone_other AS phone_other, u.phone_home AS phone_home, u.phone_mobile AS phone_mobile, u.phone_work AS phone_work
 188                                 FROM prospect_lists_prospects plp
 189                                 INNER JOIN users u ON plp.related_id=u.id
 190                                 LEFT JOIN email_addr_bean_rel ear ON  ear.bean_id=u.id AND ear.deleted=0
 191                                 LEFT JOIN email_addresses ea ON ear.email_address_id=ea.id
 192                                 WHERE plp.prospect_list_id = $record_id AND plp.deleted=0
 193                                 AND u.deleted=0
 194                                 AND (ear.deleted=0 OR ear.deleted IS NULL)";
 195
 196                 $contacts_query = "SELECT c.id AS id, 'Contacts' AS related_type, '' AS \"name\", c.first_name AS first_name, c.last_name AS last_name,c.title AS title, c.salutation AS salutation,
 197                                 c.primary_address_street AS primary_address_street,c.primary_address_city AS primary_address_city, c.primary_address_state AS primary_address_state,  c.primary_address_postalcode AS primary_address_postalcode, c.primary_address_country AS primary_address_country,
 198                                 a.name AS account_name,
 199                                 ea.email_address AS email_address, ea.invalid_email AS invalid_email, ea.opt_out AS opt_out, ea.deleted AS ea_deleted, ear.deleted AS ear_deleted, ear.primary_address AS primary_address,
 200                                 c.do_not_call AS do_not_call, c.phone_fax AS phone_fax, c.phone_other AS phone_other, c.phone_home AS phone_home, c.phone_mobile AS phone_mobile, c.phone_work AS phone_work
 201 FROM prospect_lists_prospects plp
 202                                 INNER JOIN contacts c ON plp.related_id=c.id LEFT JOIN accounts_contacts ac ON ac.contact_id=c.id LEFT JOIN accounts a ON ac.account_id=a.id
 203                                 LEFT JOIN email_addr_bean_rel ear ON ear.bean_id=c.id AND ear.deleted=0
 204                                 LEFT JOIN email_addresses ea ON ear.email_address_id=ea.id
 205                                 WHERE plp.prospect_list_id = $record_id AND plp.deleted=0
 206                                 AND c.deleted=0
 207                 AND (ear.deleted=0 OR ear.deleted IS NULL)";
 208
 209                 $prospects_query = "SELECT p.id AS id, 'Prospects' AS related_type, '' AS \"name\", p.first_name AS first_name, p.last_name AS last_name,p.title AS title, p.salutation AS salutation,
 210                                 p.primary_address_street AS primary_address_street,p.primary_address_city AS primary_address_city, p.primary_address_state AS primary_address_state,  p.primary_address_postalcode AS primary_address_postalcode, p.primary_address_country AS primary_address_country,
 211                                 p.account_name AS account_name,
 212                                 ea.email_address AS email_address, ea.invalid_email AS invalid_email, ea.opt_out AS opt_out, ea.deleted AS ea_deleted, ear.deleted AS ear_deleted, ear.primary_address AS primary_address,
 213                                 p.do_not_call AS do_not_call, p.phone_fax AS phone_fax, p.phone_other AS phone_other, p.phone_home AS phone_home, p.phone_mobile AS phone_mobile, p.phone_work AS phone_work
 214                                 FROM prospect_lists_prospects plp
 215                                 INNER JOIN prospects p ON plp.related_id=p.id
 216                                 LEFT JOIN email_addr_bean_rel ear ON  ear.bean_id=p.id AND ear.deleted=0
 217                                 LEFT JOIN email_addresses ea ON ear.email_address_id=ea.id
 218                                 WHERE plp.prospect_list_id = $record_id  AND plp.deleted=0
 219                                 AND p.deleted=0
 220                                 AND (ear.deleted=0 OR ear.deleted IS NULL)";
 221
 222                 $accounts_query = "SELECT a.id AS id, 'Accounts' AS related_type, a.name AS \"name\", '' AS first_name, '' AS last_name,'' AS title, '' AS salutation,
 223                                 a.billing_address_street AS primary_address_street,a.billing_address_city AS primary_address_city, a.billing_address_state AS primary_address_state, a.billing_address_postalcode AS primary_address_postalcode, a.billing_address_country AS primary_address_country,
 224                                 '' AS account_name,
 225                                 ea.email_address AS email_address, ea.invalid_email AS invalid_email, ea.opt_out AS opt_out, ea.deleted AS ea_deleted, ear.deleted AS ear_deleted, ear.primary_address AS primary_address,
 226                                 0 AS do_not_call, a.phone_fax as phone_fax, a.phone_alternate AS phone_other, '' AS phone_home, '' AS phone_mobile, a.phone_office AS phone_office
 227                                 FROM prospect_lists_prospects plp
 228                                 INNER JOIN accounts a ON plp.related_id=a.id
 229                                 LEFT JOIN email_addr_bean_rel ear ON  ear.bean_id=a.id AND ear.deleted=0
 230                                 LEFT JOIN email_addresses ea ON ear.email_address_id=ea.id
 231                                 WHERE plp.prospect_list_id = $record_id  AND plp.deleted=0
 232                                 AND a.deleted=0
 233                                 AND (ear.deleted=0 OR ear.deleted IS NULL)";
 234                 $order_by = "ORDER BY related_type, id, primary_address DESC";
 235                 $query = "$leads_query UNION ALL $users_query UNION ALL $contacts_query UNION ALL $prospects_query UNION ALL $accounts_query $order_by";
 236                 return $query;
 237         }
 238
 239         function save_relationship_changes($is_update)
 240     {
 241         parent::save_relationship_changes($is_update);
 242                 if($this->lead_id != "")
 243                         $this->set_prospect_relationship($this->id, $this->lead_id, "lead");
 244         if($this->contact_id != "")
 245                 $this->set_prospect_relationship($this->id, $this->contact_id, "contact");
 246         if($this->prospect_id != "")
 247                 $this->set_prospect_relationship($this->id, $this->contact_id, "prospect");
 248     }
 249
 250         function set_prospect_relationship($prospect_list_id, &$link_ids, $link_name)
 251         {
 252                 $link_field = sprintf("%s_id", $link_name);
 253
 254                 foreach($link_ids as $link_id)
 255                 {
 256                         $this->set_relationship('prospect_lists_prospects', array( $link_field=>$link_id, 'prospect_list_id'=>$prospect_list_id ));
 257                 }
 258         }
 259
 260         function set_prospect_relationship_single($prospect_list_id, $link_id, $link_name)
 261         {
 262                 $link_field = sprintf("%s_id", $link_name);
 263
 264                 $this->set_relationship('prospect_lists_prospects', array( $link_field=>$link_id, 'prospect_list_id'=>$prospect_list_id ));
 265         }
 266
 267
 268         function clear_prospect_relationship($prospect_list_id, $link_id, $link_name)
 269         {
 270                 $link_field = sprintf("%s_id", $link_name);
 271                 $where_clause = " AND $link_field = '$link_id' ";
 272
 273                 $query = sprintf("DELETE FROM prospect_lists_prospects WHERE prospect_list_id='%s' AND deleted = '0' %s", $prospect_list_id, $where_clause);
 274
 275                 $this->db->query($query, true, "Error clearing prospect/prospect_list relationship: ");
 276         }
 277
 278
 279         function mark_relationships_deleted($id)
 280         {
 281         }
 282
 283         function fill_in_additional_list_fields()
 284         {
 285         }
 286
 287         function fill_in_additional_detail_fields()
 288         {
 289                 parent::fill_in_additional_detail_fields();
 290         $this->entry_count = $this->get_entry_count();
 291         }
 292
 293
 294         function update_currency_id($fromid, $toid){
 295         }
 296
 297
 298         function get_entry_count()
 299         {
 300                 $query = "SELECT count(*) AS num FROM prospect_lists_prospects WHERE prospect_list_id='$this->id' AND deleted = '0'";
 301                 $result = $this->db->query($query, true, "Grabbing prospect_list entry count");
 302
 303                 $row = $this->db->fetchByAssoc($result);
 304
 305                 if($row)
 306                         return $row['num'];
 307                 else
 308                         return 0;
 309         }
 310
 311
 312         function get_list_view_data(){
 313                 $temp_array = $this->get_list_view_array();
 314                 $temp_array["ENTRY_COUNT"] = $this->get_entry_count();
 315                 return $temp_array;
 316         }
 317         /**
 318                 builds a generic search based on the query string using or
 319                 do not include any $this-> because this is called on without having the class instantiated
 320         */
 321         function build_generic_where_clause ($the_query_string)
 322         {
 323                 $where_clauses = Array();
 324                 $the_query_string = $GLOBALS['db']->quote($the_query_string);
 325                 array_push($where_clauses, "prospect_lists.name like '$the_query_string%'");
 326
 327                 $the_where = "";
 328                 foreach($where_clauses as $clause)
 329                 {
 330                         if($the_where != "") $the_where .= " or ";
 331                         $the_where .= $clause;
 332                 }
 333
 334
 335                 return $the_where;
 336         }
 337
 338         function save($check_notify = FALSE) {
 339
 340                 return parent::save($check_notify);
 341
 342         }
 343
 344          function bean_implements($interface){
 345                 switch($interface){
 346                         case 'ACL':return true;
 347                 }
 348                 return false;
 349         }
 350
 351 }
 352
 353
 354
 355
 356
 357 ?>